SQL Server解析XML数据的方法详解

本文实例讲述了sql server解析xml数据的方法。分享给大家供大家参考，具体如下：

--5.读取xml
--下面为多种方法从xml中读取email
declare @x xml
select @x = '
<people>
  <dongsheng>
    <info name="email">[e-mail]</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"/>

sql server 中对xml数据的五种基本操作

1.xml.exist
   输入为xquery表达式，返回0，1或是null。0表示不存在，1表示存在，null表示输入为空
2.xml.value
   输入为xquery表达式，返回一个sql server标量值
3.xml.query
   输入为xquery表达式，返回一个sql server xml类型流
4.xml.nodes
   输入为xquery表达式，返回一个xml格式文档的一列行集
5.xml.modify

使用xquery表达式对xml的节点进行insert , update 和 delete 操作。

下面通过例子对上面的五种操作进行说明：

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>'

1. xml.exist

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

2.xml.value

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)')

结果集为：
windows step by stepbill zack49.99   developer   null
3.xml.query

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>

4.xml.nodes

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>[e-mail]</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  [e-mail]
1001  phone  [phone]
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>[e-mail]</email>
    <phone>678945546</phone>
    <qq>36575</qq>
    <addr>36575</addr>
</people>
</peoples>'
execute sp_exportxml @x,'/*/*/*'

希望本文所述对大家sql server数据库程序设计有所帮助。