本文实例讲述了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数据库程序设计有所帮助。
【SQL Server解析XML数据的方法详解】相关文章：
★ win2003 Server配置SQL Server 2005远程连接的方法
★ SQL Server 中 ROR XML PATH 用法
★ SQL Server 2008 R2安装配置方法图文教程