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

本文实例讲述了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）"）-- 方法3SELECTC.value（".","varchar（30）"）FROM @x.nodes（"/People/dongsheng/Info[@Name="Email"]"） T（C）-- 方法4SELECTC.value（"（Info[@Name="Email"]）[1]","varchar（30）"）FROM @x.nodes（"/People/dongsheng"） T（C）-- 方法5SELECTC.value（"（dongsheng/Info[@Name="Email"]）[1]","varchar（30）"）FROM @x.nodes（"/People"） T（C）-- 方法6SELECTC.value（".","varchar（30）"）FROM @x.nodes（"/People/dongsheng/Info"） T（C）WHERE C.value（"（.[@Name="Email"]）[1]","varchar（30）"） IS NOT NULL-- 方法7SELECTC.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>"SELECTv.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的人员信息--方法1SELECTC.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（"/Employees/Employee[@dept="IT"]/Info"） T（C）/*NAME SEXQQ---------- ---------- --------------------dongsheng 男 5454545454土豆 女 5345454554*/--方法2SELECTC.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"]/*"） T（C）/*NAME SEXQQ---------- ---------- --------------------dongsheng 男 5454545454土豆 女 5345454554*/--查询出IT部门type为Permanent的员工SELECTC.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）/*NAME SEXQQ---------- ---------- --------------------dongsheng 男 5454545454*/--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/*<Peoples> <People><NAME>土豆</NAME><QQ>5345454554</QQ> </People></Peoples>*/--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）/*NAME--------------------dongsheng土豆choushuigou*/--20使用通配符读取元素值--读取根元素的值DECLARE @x1 XMLSELECT @x1 = "<People>dongsheng</People>"SELECT @x1.value（"（/*/text（））[1]","VARCHAR（20）"） AS People --星号*代表一个元素/*People--------------------dongsheng*/--读取第二层元素的值DECLARE@x XMLSELECT @x = " <People> <NAME>dongsheng</NAME> <SEX>男</SEX> <QQ>423545</QQ> </People>"SELECT@x.value（"（/*/*/text（））[1]","VARCHAR（20）"） AS NAME/*NAME--------------------dongsheng*/--读取第二个子元素的值DECLARE@x XMLSELECT @x = " <People> <NAME>dongsheng</NAME> <SEX>男</SEX> <QQ>423545</QQ> </People>"SELECT@x.value（"（/*/*/text（））[2]","VARCHAR（20）"） AS SEX/*SEX--------------------男*/--读取所有第二层子元素值DECLARE@x XMLSELECT @x = " <People> <NAME>dongsheng</NAME> <SEX>男</SEX> <QQ>423545</QQ> </People>"SELECTC.value（".","VARCHAR（20）"） AS valueFROM @x.nodes（"/*/*"） T（C）/*value--------------------dongsheng男423545*/--21.使用通配符读取元素名称DECLARE @x XMLSELECT @x = "<People>dongsheng</People>"SELECT@x.value（"local-name（/*[1]）","VARCHAR（20）"） AS ElementName/*ElementName--------------------People*/--读取根下第一个元素的名称和值DECLARE@x XMLSELECT @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 XMLSELECT @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 XMLSELECT @x = " <People> <NAME>dongsheng</NAME> <SEX>男</SEX> </People>"SELECTC.value（"local-name（.）","VARCHAR（20）"） AS ElementName,C.value（".","VARCHAR（20）"） AS ElementValueFROM @x.nodes（"/*/*"） T（C）/*ElementName ElementValue-------------------- --------------------NAME dongshengSEX 男*/---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/*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 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/*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-------------------- ---------------------------- -----------------------------134*/--返回没个节点的属性值SELECTC.value（"count（./@*）","INT"） AS AttributeCountFROM @x.nodes（"/*/*"） T（C）/*AttributeCount--------------34*/--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/*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 INTSELECT @Elepos=2,@Attpos = 3SELECT@x.value（"local-name（（/Employees/Employee[sql:variable（"@Elepos"）]/@*[position（）=sql:variable（"@Attpos"）]）[1]）","VARCHAR（20）"） AS AttName/*AttName--------------------QQ*/--25.判断是XML中否存在相应的属性DECLARE@x XMLSELECT @x = "<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>"IF @x.exist（"/Employee/@NAME"） = 1SELECT "Exists" AS ResultELSESELECT "Does not exist" AS Result/*Result------Exists*/--传递变量判断是否存在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"）]"） = 1SELECT "Exists" AS ResultELSESELECT "Does not exist" AS Result/*Result------Exists*/--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 BEGINSELECT@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: " + @attNamePRINT "Attribute Value: " + @attValuePRINT ""-- increment the counter variableSELECT @cnt = @cnt + 1END/*Attribute Position: 1Attribute Name: NAMEAttribute Value: 土豆Attribute Position: 2Attribute Name: SEXAttribute Value: 女Attribute Position: 3Attribute Name: QQAttribute Value: 5345454554Attribute Position: 4Attribute Name: TELAttribute Value: 13954697895*/--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]"）/*<Employee NAME="dongsheng" SEX="男" QQ="5454545454" />*/SELECT @x.query（"（/Employees/Employee）[position（）=2]"）/*<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895" />*/--通过变量获取指定位置的子元素DECLARE @i INTSELECT @i = 2SELECT @x.query（"（/Employees/Employee）[sql:variable（"@i"）]"）--orSELECT @x.query（"（/Employees/Employee）[position（）=sql:variable（"@i"）]"）/*<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895" />*/--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 BEGINSELECT@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 variableSELECT @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（SELECTC.value（"local-name（.）","VARCHAR（20）"） AS attrName,C.value（".","VARCHAR（20）"） AS attrValueFROM @x.nodes（"/*/*/*"） T（C） --第三层） as p/*1001Email1dongsheng@xxyy.com1001Phone6789455461001QQ 365751001Addr36575*/

/* 解析XML存储过程*/ALTER PROCEDURE [dbo].[sp_ExportXml] @x xml , @layerstr nvarchar（max）ASDECLARE @sql nvarchar（max）BEGIN set arithabort onset @sql="select p.* FROM（SELECTC.value（""local-name（.）"",""VARCHAR（20）""） AS attrName,C.value（""."",""VARCHAR（20）""） AS attrValueFROM @xmlParas.nodes（"""+@layerstr+"""） T（C）） as p"--print @sql EXECUTE sp_executesql @sql, N"@xmlParas as xml",@xmlParas=@xEND

DECLARE @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数据库程序设计有所帮助。