本文实例讲述了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"/>
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>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,'/*/*/*'
希望本文所述对大家sql server数据库程序设计有所帮助。