数据库教程:SQL Server解析XML数据的方法详解

  --5.读取xml  --下面为多种方法从xml中读取email  declare @x xml  select @x = '  <people>    <dongsheng>      <info name="email">dongsheng@xxyy.com</info>      <info name="phone">678945546</info>      <info name="qq">36575</info>    </dongsheng>  </people>'  -- 方法1  select @x.value('data(/people/dongsheng/info[@name="email"])[1]', 'varchar(30)')  -- 方法2  select @x.value('(/people/dongsheng/info[@name="email"])[1]', 'varchar(30)')  -- 方法3  select    c.value('.','varchar(30)')  from @x.nodes('/people/dongsheng/info[@name="email"]') t(c)  -- 方法4  select    c.value('(info[@name="email"])[1]','varchar(30)')  from @x.nodes('/people/dongsheng') t(c)  -- 方法5  select    c.value('(dongsheng/info[@name="email"])[1]','varchar(30)')  from @x.nodes('/people') t(c)  -- 方法6  select    c.value('.','varchar(30)')  from @x.nodes('/people/dongsheng/info') t(c)  where c.value('(.[@name="email"])[1]','varchar(30)') is not null  -- 方法7  select    c.value('.','varchar(30)')  from @x.nodes('/people/dongsheng/info') t(c)  where c.exist('(.[@name="email"])[1]') = 1  --6.reading values from an xml variable  declare @x xml  select @x =  '<peoples>    <people name="tudou" sex="女" />    <people name="choushuigou" sex="女"/>    <people name="dongsheng" sex="男" />  </peoples>'  select    v.value('@name[1]','varchar(20)') as name,    v.value('@sex[1]','varchar(20)') as sex  from @x.nodes('/peoples/people') x(v)  --7.多属性过滤  declare @x xml  select @x = '  <employees>   <employee id="1234" dept="it" type="合同工">    <info name="dongsheng" sex="男" qq="5454545454"/>   </employee>   <employee id="5656" dept="it" type="临时工">    <info name="土豆" sex="女" qq="5345454554"/>   </employee>   <employee id="3242" dept="市场" type="合同工">    <info name="choushuigou" sex="女" qq="54543545"/>   </employee>  </employees>'  --查询dept为it的人员信息    --方法1    select      c.value('@name[1]','varchar(10)') as name,      c.value('@sex[1]','varchar(10)') as sex,      c.value('@qq[1]','varchar(20)') as qq    from @x.nodes('/employees/employee[@dept="it"]/info') t(c)    /*    name   sex    qq    ---------- ---------- --------------------    dongsheng 男     5454545454    土豆   女     5345454554    */    --方法2    select      c.value('@name[1]','varchar(10)') as name,      c.value('@sex[1]','varchar(10)') as sex,      c.value('@qq[1]','varchar(20)') as qq    from @x.nodes('//employee[@dept="it"]/*') t(c)    /*    name   sex    qq    ---------- ---------- --------------------    dongsheng 男     5454545454    土豆   女     5345454554    */  --查询出it部门type为permanent的员工  select    c.value('@name[1]','varchar(10)') as name,    c.value('@sex[1]','varchar(10)') as sex,    c.value('@qq[1]','varchar(20)') as qq  from @x.nodes('//employee[@dept="it"][@type="合同工"]/*') t(c)  /*    name   sex    qq    ---------- ---------- --------------------    dongsheng 男     5454545454  */  --12.从xml变量中删除元素  declare @x xml  select @x = '  <peoples>   <people>     <name>土豆</name>     <sex>男</sex>     <qq>5345454554</qq>   </people>  </peoples>'  set @x.modify('    delete (/peoples/people/sex)[1]'   )  select @x  /*  <peoples>   <people>    <name>土豆</name>    <qq>5345454554</qq>   </people>  </peoples>  */  --19.读取指定变量元素的值  declare @x xml  select @x = '  <peoples>   <people>     <name>dongsheng</name>     <sex>男</sex>     <qq>423545</qq>   </people>   <people>     <name>土豆</name>     <sex>男</sex>     <qq>123133</qq>   </people>   <people>     <name>choushuigou</name>     <sex>女</sex>     <qq>54543545</qq>   </people>  </peoples>  '  declare @elementname varchar(20)  select @elementname = 'name'  select c.value('.','varchar(20)') as name  from @x.nodes('/peoples/people/*[local-name()=sql:variable("@elementname")]') t(c)  /*  name  --------------------  dongsheng  土豆  choushuigou  */  --20使用通配符读取元素值  --读取根元素的值  declare @x1 xml  select @x1 = '<people>dongsheng</people>'  select @x1.value('(/*/text())[1]','varchar(20)') as people --星号*代表一个元素  /*  people  --------------------  dongsheng  */  --读取第二层元素的值  declare  @x xml  select @x = '   <people>     <name>dongsheng</name>     <sex>男</sex>     <qq>423545</qq>   </people>'  select    @x.value('(/*/*/text())[1]','varchar(20)') as name  /*  name  --------------------  dongsheng  */  --读取第二个子元素的值  declare  @x xml  select @x = '   <people>     <name>dongsheng</name>     <sex>男</sex>     <qq>423545</qq>   </people>'  select    @x.value('(/*/*/text())[2]','varchar(20)') as sex  /*  sex  --------------------  男  */  --读取所有第二层子元素值  declare  @x xml  select @x = '   <people>     <name>dongsheng</name>     <sex>男</sex>     <qq>423545</qq>   </people>'  select    c.value('.','varchar(20)') as value  from @x.nodes('/*/*') t(c)  /*  value  --------------------  dongsheng  男  423545  */  --21.使用通配符读取元素名称  declare @x xml  select @x = '<people>dongsheng</people>'  select    @x.value('local-name(/*[1])','varchar(20)') as elementname  /*  elementname  --------------------  people  */  --读取根下第一个元素的名称和值  declare  @x xml  select @x = '   <people>     <name>dongsheng</name>     <sex>男</sex>   </people>'  select    @x.value('local-name((/*/*)[1])','varchar(20)') as elementname,    @x.value('(/*/*/text())[1]','varchar(20)') as elementvalue  /*  elementname     elementvalue  -------------------- --------------------  name         dongsheng  */  --读取根下第二个元素的名称和值  declare  @x xml  select @x = '   <people>     <name>dongsheng</name>     <sex>男</sex>   </people>'  select    @x.value('local-name((/*/*)[2])','varchar(20)') as elementname,    @x.value('(/*/*/text())[2]','varchar(20)') as elementvalue  /*  elementname     elementvalue  -------------------- --------------------  sex         男  */  --读取根下所有的元素名称和值  declare  @x xml  select @x = '   <people>     <name>dongsheng</name>     <sex>男</sex>   </people>'  select    c.value('local-name(.)','varchar(20)') as elementname,    c.value('.','varchar(20)') as elementvalue  from @x.nodes('/*/*') t(c)  /*  elementname     elementvalue  -------------------- --------------------  name         dongsheng  sex         男  */  ---22.查询元素数量  --如下peoples根节点下有个people子节点。  declare @x xml  select @x = '  <peoples>   <people>     <name>dongsheng</name>     <sex>男</sex>   </people>   <people>     <name>土豆</name>     <sex>男</sex>   </people>   <people>     <name>choushuigou</name>     <sex>女</sex>   </people>  </peoples>  '  select  @x.value('count(/peoples/people)','int') as children  /*  children  -----------  3  */  --如下peoples根节点下第一个子节点people下子节点的数量  select  @x.value('count(/peoples/people[1]/*)','int') as children  /*  children  -----------  2  */  --某些时候我们可能不知道根节点和子节点的名称，可以用通配符来代替。  select  @x.value('count(/*/*)','int') as childrenofroot,       @x.value('count(/*/*[1]/*)','int') as childrenoffirstchildelement  /*  childrenofroot childrenoffirstchildelement  -------------- ---------------------------  3       2  */  --23.查询属性的数量  declare @x xml  select @x = '  <employees dept="it">    <employee name="dongsheng" sex="男" qq="5454545454"/>    <employee name="土豆" sex="女" qq="5345454554" tel="13954697895"/>  </employees>'  --查询跟节点的属性数量  select  @x.value('count(/employees/@*)','int') as attributecountofroot  /*  attributecountofroot  --------------------  1  */  --第一个employee节点的属性数量  select  @x.value('count(/employees/employee[1]/@*)','int') as attributecountoffirstelement  /*  attributecountoffirstelement  ----------------------------  3  */  --第二个employee节点的属性数量  select  @x.value('count(/employees/employee[2]/@*)','int') as attributecountofseconfelement  /*  attributecountofseconfelement  -----------------------------  4  */  --如果不清楚节点名称可以用*通配符代替  select  @x.value('count(/*/@*)','int') as attributecountofroot      ,@x.value('count(/*/*[1]/@*)','int') as attributecountoffirstelement      ,@x.value('count(/*/*[2]/@*)','int') as attributecountofseconfelement  /*  attributecountofroot attributecountoffirstelement attributecountofseconfelement  -------------------- ---------------------------- -----------------------------  1          3              4  */  --返回没个节点的属性值  select  c.value('count(./@*)','int') as attributecount  from @x.nodes('/*/*') t(c)  /*  attributecount  --------------  3  4  */  --24.返回给定位置的属性值或者名称  declare @x xml  select @x = '  <employees dept="it">    <employee name="dongsheng" sex="男" qq="5454545454"/>    <employee name="土豆" sex="女" qq="5345454554" tel="13954697895"/>  </employees>'  --返回第一个employee节点的第一个位置的属性值  select  @x.value('(/employees/employee[1]/@*[position()=1])[1]','varchar(20)') as attvalue  /*  attvalue  --------------------  dongsheng  */  --返回第二个employee节点的第四个位置的属性值  select  @x.value('(/employees/employee[2]/@*[position()=4])[1]','varchar(20)') as attvalue  /*  attvalue  --------------------  13954697895  */  --返回第一个元素的第三个属性值  select  @x.value('local-name((/employees/employee[1]/@*[position()=3])[1])','varchar(20)') as attname  /*  attname  --------------------  qq  */  --返回第二个元素的第四个属性值  select  @x.value('local-name((/employees/employee[2]/@*[position()=4])[1])','varchar(20)') as attname  /*  attname  --------------------  tel  */  --通过变量传递位置返回属性值  declare @elepos int,@attpos int  select @elepos=2,@attpos = 3  select  @x.value('local-name((/employees/employee[sql:variable("@elepos")]/@*[position()=sql:variable("@attpos")])[1])','varchar(20)') as attname  /*  attname  --------------------  qq  */  --25.判断是xml中否存在相应的属性  declare  @x xml  select @x = '<employee name="土豆" sex="女" qq="5345454554" tel="13954697895"/>'  if @x.exist('/employee/@name') = 1    select 'exists' as result  else    select 'does not exist' as result  /*  result  ------  exists  */  --传递变量判断是否存在  declare  @x xml  select @x = '<employee name="土豆" sex="女" qq="5345454554" tel="13954697895"/>'  declare @att varchar(20)  select @att = 'qq'  if @x.exist('/employee/@*[local-name()=sql:variable("@att")]') = 1    select 'exists' as result  else    select 'does not exist' as result  /*  result  ------  exists  */  --26.循环遍历元素的所有属性  declare  @x xml  select @x = '<employee name="土豆" sex="女" qq="5345454554" tel="13954697895"/>'  declare    @cnt int,    @totcnt int,    @attname varchar(30),    @attvalue varchar(30)  select    @cnt = 1,    @totcnt = @x.value('count(/employee/@*)','int')--获得属性总数量  -- loop  while @cnt <= @totcnt begin    select      @attname = @x.value(        'local-name((/employee/@*[position()=sql:variable("@cnt")])[1])',        'varchar(30)'),      @attvalue = @x.value(        '(/employee/@*[position()=sql:variable("@cnt")])[1]',        'varchar(30)')    print 'attribute position: ' + cast(@cnt as varchar)    print 'attribute name: ' + @attname    print 'attribute value: ' + @attvalue    print ''    -- increment the counter variable    select @cnt = @cnt + 1  end  /*  attribute position: 1  attribute name: name  attribute value: 土豆  attribute position: 2  attribute name: sex  attribute value: 女  attribute position: 3  attribute name: qq  attribute value: 5345454554  attribute position: 4  attribute name: tel  attribute value: 13954697895  */  --27.返回指定位置的子元素  declare @x xml  select @x = '  <employees dept="it">    <employee name="dongsheng" sex="男" qq="5454545454"/>    <employee name="土豆" sex="女" qq="5345454554" tel="13954697895"/>  </employees>'  select @x.query('(/employees/employee)[1]')  /*  <employee name="dongsheng" sex="男" qq="5454545454" />  */  select @x.query('(/employees/employee)[position()=2]')  /*  <employee name="土豆" sex="女" qq="5345454554" tel="13954697895" />  */  --通过变量获取指定位置的子元素  declare @i int  select @i = 2  select @x.query('(/employees/employee)[sql:variable("@i")]')  --or  select @x.query('(/employees/employee)[position()=sql:variable("@i")]')  /*  <employee name="土豆" sex="女" qq="5345454554" tel="13954697895" />  */  --28.循环遍历获得所有子元素  declare @x xml  select @x = '  <employees dept="it">    <employee name="dongsheng" sex="男" qq="5454545454"/>    <employee name="土豆" sex="女" qq="5345454554" tel="13954697895"/>  </employees>'  declare    @cnt int,    @totcnt int,    @child xml  -- counter variables  select    @cnt = 1,    @totcnt = @x.value('count(/employees/employee)','int')  -- loop  while @cnt <= @totcnt begin    select      @child = @x.query('/employees/employee[position()=sql:variable("@cnt")]')    print 'processing child element: ' + cast(@cnt as varchar)    print 'child element: ' + cast(@child as varchar(100))    print ''    -- incremet the counter variable    select @cnt = @cnt + 1  end  /*  processing child element: 1  child element: <employee name="dongsheng" sex="男" qq="5454545454"/>  processing child element: 2  child element: <employee name="土豆" sex="女" qq="5345454554" tel="13954697895"/>    

  declare @xmlvar xml = '  <catalog>      <book category="itpro">         <title>windows step by step</title>         <author>bill zack</author>         <price>49.99</price>      </book>      <book category="developer">         <title>developing ado .net</title>         <author>andrew brust</author>         <price>39.93</price>      </book>      <book category="itpro">         <title>windows cluster server</title>         <author>stephen forte</author>         <price>59.99</price>      </book>  </catalog>'  

  select @xmlvar.exist('/catalog/book')-----返回1  select @xmlvar.exist('/catalog/book/@category')-----返回1  select @xmlvar.exist('/catalog/book1')-----返回0  set @xmlvar = null  select @xmlvar.exist('/catalog/book')-----返回null  

  select @xmlvar.value('/catalog[1]/book[1]','varchar(max)')  select @xmlvar.value('/catalog[1]/book[2]/@category','varchar(max)')  select @xmlvar.value('/catalog[2]/book[1]','varchar(max)')  

  select @xmlvar.query('/catalog[1]/book')  select @xmlvar.query('/catalog[1]/book[1]')  select @xmlvar.query('/catalog[1]/book[2]/author')  

  <book category="itpro">   <title>windows step by step</title>   <author>bill zack</author>   <price>49.99</price>  </book>  <book category="developer">   <title>developing ado .net</title>   <author>andrew brust</author>   <price>39.93</price>  </book>  <book category="itpro">   <title>windows cluster server</title>   <author>stephen forte</author>   <price>59.99</price>  </book>  <book category="itpro">   <title>windows step by step</title>   <author>bill zack</author>   <price>49.99</price>  </book>  <author>andrew brust</author>  

  select t.c.query('.') as result from @xmlvar.nodes('/catalog/book') as t(c)  select t.c.query('title') as result from @xmlvar.nodes('/catalog/book') as t(c)  

  <book category="itpro"><title>windows step by step</title><author>bill …………  <book category="developer"><title>developing ado .net</title><author>andrew …………  <book category="itpro"><title>windows cluster server</title><author>stephen …………  <title>windows step by step</title>  <title>developing ado .net</title>  <title>windows cluster server</title>  

  set arithabort on  declare @x xml  select @x = '<peoples>  <people>      <email>1dongsheng@xxyy.com</email>      <phone>678945546</phone>      <qq>36575</qq>      <addr>36575</addr>  </people>  </peoples>'  -- 方法1  select 1001 as peopleid, p.* from(  select    c.value('local-name(.)','varchar(20)') as attrname,    c.value('.','varchar(20)') as attrvalue  from @x.nodes('/*/*/*') t(c) --第三层  ) as p  /*  1001  email  1dongsheng@xxyy.com  1001  phone  678945546  1001  qq 36575  1001  addr  36575  */    

  /*   解析xml存储过程  */  alter procedure [dbo].[sp_exportxml]   @x xml ,   @layerstr nvarchar(max)  as    declare @sql nvarchar(max)  begin     set arithabort on      set @sql='select p.* from(      select          c.value(''local-name(.)'',''varchar(20)'') as attrname,          c.value(''.'',''varchar(20)'') as attrvalue      from @xmlparas.nodes('''+@layerstr+''') t(c)      ) as p'    --print @sql     execute sp_executesql @sql, n'@xmlparas as xml',@xmlparas=@x  end    

  declare @x xml  select @x =  '<peoples>  <people>      <email>1dongsheng@xxyy.com</email>      <phone>678945546</phone>      <qq>36575</qq>      <addr>36575</addr>  </people>  </peoples>'  execute sp_exportxml @x,'/*/*/*'