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

--5.读取XML--下面为多种方法从XML中读取EMAILDECLARE @x XMLSELECT @x = '<People> <dongsheng> <Info Name="Email">dongsheng@xxyy.com</Info> <Info Name="Phone">678945546</Info> <Info Name="qq">36575</Info> </dongsheng></People>'-- 方法1SELECT @x.value('data(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')-- 方法2SELECT @x.value('(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')-- 方法3SELECT C.value('.','varchar(30)')FROM @x.nodes('/People/dongsheng/Info[@Name="Email"]') T(C)-- 方法4SELECT C.value('(Info[@Name="Email"])[1]','varchar(30)')FROM @x.nodes('/People/dongsheng') T(C)-- 方法5SELECT C.value('(dongsheng/Info[@Name="Email"])[1]','varchar(30)')FROM @x.nodes('/People') T(C)-- 方法6SELECT C.value('.','varchar(30)')FROM @x.nodes('/People/dongsheng/Info') T(C)WHERE C.value('(.[@Name="Email"])[1]','varchar(30)') IS NOT NULL-- 方法7SELECT 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 variableDECLARE @x XMLSELECT @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 SexFROM @x.nodes('/Peoples/People') x(v)--7.多属性过滤DECLARE @x XMLSELECT @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) --方法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) --查询出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 QQFROM @x.nodes('//Employee[@dept="IT"][@type="合同工"]/*') T(C)--12.从XML变量中删除元素DECLARE @x XMLSELECT @x = '<Peoples> <People> <NAME>土豆</NAME> <SEX>男</SEX> <QQ>5345454554</QQ> </People></Peoples>'SET @x.modify(' delete (/Peoples/People/SEX)[1]' )SELECT @x--19.读取指定变量元素的值DECLARE @x XMLSELECT @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 NAMEFROM @x.nodes('/Peoples/People/*[local-name()=sql:variable("@ElementName")]') T(C)--20使用通配符读取元素值--读取根元素的值DECLARE @x1 XMLSELECT @x1 = '<People>dongsheng</People>'SELECT @x1.value('(/*/text())[1]','VARCHAR(20)') AS People --星号*代表一个元素--读取第二层元素的值DECLARE @x XMLSELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> <QQ>423545</QQ> </People>'SELECT @x.value('(text())[1]','VARCHAR(20)') AS NAME--读取第二个子元素的值DECLARE @x XMLSELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> <QQ>423545</QQ> </People>'SELECT @x.value('(text())[2]','VARCHAR(20)') AS SEX--读取所有第二层子元素值DECLARE @x XMLSELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> <QQ>423545</QQ> </People>'SELECT C.value('.','VARCHAR(20)') AS valueFROM @x.nodes('/*/*') T(C)--21.使用通配符读取元素名称DECLARE @x XMLSELECT @x = '<People>dongsheng</People>'SELECT @x.value('local-name(/*[1])','VARCHAR(20)') AS ElementName--读取根下第一个元素的名称和值DECLARE @x XMLSELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> </People>'SELECT @x.value('local-name((/**/text())[1]','VARCHAR(20)') AS ElementValue--读取根下第二个元素的名称和值DECLARE @x XMLSELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> </People>'SELECT @x.value('local-name((/**/text())[2]','VARCHAR(20)') AS ElementValue--读取根下所有的元素名称和值DECLARE @x XMLSELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> </People>'SELECT C.value('local-name(.)','VARCHAR(20)') AS ElementName, C.value('.','VARCHAR(20)') AS ElementValueFROM @x.nodes('/*/*') T(C)---22.查询元素数量--如下Peoples根节点下有个People子节点。DECLARE @x XMLSELECT @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--如下Peoples根节点下第一个子节点People下子节点的数量SELECT @x.value('count(/Peoples/People[1]/*)','INT') AS Children--某些时候我们可能不知道根节点和子节点的名称，可以用通配符来代替。SELECT @x.value('count(/**[1]/*)','INT') AS ChildrenOfFirstChildElement--23.查询属性的数量DECLARE @x XMLSELECT @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--第一个Employee节点的属性数量SELECT @x.value('count(/Employees/Employee[1]/@*)','INT') AS AttributeCountOfFirstElement--第二个Employee节点的属性数量SELECT @x.value('count(/Employees/Employee[2]/@*)','INT') AS AttributeCountOfSeconfElement--如果不清楚节点名称可以用*通配符代替SELECT @x.value('count(/*/@*)','INT') AS AttributeCountOfRoot ,@x.value('count(/*/*[1]/@*)','INT') AS AttributeCountOfFirstElement ,@x.value('count(/*/*[2]/@*)','INT') AS AttributeCountOfSeconfElement--返回没个节点的属性值SELECT C.value('count(./@*)','INT') AS AttributeCountFROM @x.nodes('/*/*') T(C)--24.返回给定位置的属性值或者名称DECLARE @x XMLSELECT @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--返回第二个Employee节点的第四个位置的属性值SELECT @x.value('(/Employees/Employee[2]/@*[position()=4])[1]','VARCHAR(20)') AS AttValue--返回第一个元素的第三个属性值SELECT @x.value('local-name((/Employees/Employee[1]/@*[position()=3])[1])','VARCHAR(20)') AS AttName--返回第二个元素的第四个属性值SELECT @x.value('local-name((/Employees/Employee[2]/@*[position()=4])[1])','VARCHAR(20)') AS AttName--通过变量传递位置返回属性值DECLARE @Elepos INT,@Attpos INTSELECT @Elepos=2,@Attpos = 3SELECT @x.value('local-name((/Employees/Employee[sql:variable("@Elepos")]/@*[position()=sql:variable("@Attpos")])[1])','VARCHAR(20)') AS AttName--25.判断是XML中否存在相应的属性DECLARE @x XMLSELECT @x = '<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>'IF @x.exist('/Employee/@NAME') = 1 SELECT 'Exists' AS ResultELSE SELECT 'Does not exist' AS Result--传递变量判断是否存在DECLARE @x XMLSELECT @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 ResultELSE SELECT 'Does not exist' AS Result--26.循环遍历元素的所有属性DECLARE @x XMLSELECT @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')--获得属性总数量-- loopWHILE @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 + 1END--27.返回指定位置的子元素DECLARE @x XMLSELECT @x = '<Employees dept="IT"> <Employee NAME="dongsheng" SEX="男" QQ="5454545454"/> <Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/></Employees>'SELECT @x.query('(/Employees/Employee)[1]')SELECT @x.query('(/Employees/Employee)[position()=2]')--通过变量获取指定位置的子元素DECLARE @i INTSELECT @i = 2SELECT @x.query('(/Employees/Employee)[sql:variable("@i")]')--orSELECT @x.query('(/Employees/Employee)[position()=sql:variable("@i")]')--28.循环遍历获得所有子元素DECLARE @x XMLSELECT @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 variablesSELECT @cnt = 1, @totCnt = @x.value('count(/Employees/Employee)','INT')-- loopWHILE @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 + 1END/*Processing Child Element: 1Child element: <Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>Processing Child Element: 2Child 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')-----返回1select @XMLVar.exist('/catalog/book/@category')-----返回1select @XMLVar.exist('/catalog/book1')-----返回0set @XMLVar = nullselect @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 onDECLARE @x XMLSELECT @x = '<Peoples><People> <Email>1dongsheng@xxyy.com</Email> <Phone>678945546</Phone> <QQ>36575</QQ> <Addr>36575</Addr></People></Peoples>'-- 方法1select 1001 as peopleId, p.* FROM(SELECT C.value('local-name(.)','VARCHAR(20)') AS attrName, C.value('.','VARCHAR(20)') AS attrValueFROM @x.nodes('*') T(C) --第三层) as pALTER 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=@xENDDECLARE @x XMLSELECT @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数据库程序设计有所帮助。