SQL Server解析XML数据的方法详解
本文实例讲述了SQLServer解析XML数据的方法。分享给大家供大家参考,具体如下:
--5.读取XML --下面为多种方法从XML中读取EMAIL DECLARE@xXML SELECT@x=' <People> <dongsheng> <InfoName="Email">dongsheng@xxyy.com</Info> <InfoName="Phone">678945546</Info> <InfoName="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) WHEREC.value('(.[@Name="Email"])[1]','varchar(30)')ISNOTNULL --方法7 SELECT C.value('.','varchar(30)') FROM@x.nodes('/People/dongsheng/Info')T(C) WHEREC.exist('(.[@Name="Email"])[1]')=1 --6.ReadingvaluesfromanXMLvariable DECLARE@xXML SELECT@x= '<Peoples> <PeopleName="tudou"Sex="女"/> <PeopleName="choushuigou"Sex="女"/> <PeopleName="dongsheng"Sex="男"/> </Peoples>' SELECT v.value('@Name[1]','VARCHAR(20)')ASName, v.value('@Sex[1]','VARCHAR(20)')ASSex FROM@x.nodes('/Peoples/People')x(v) --7.多属性过滤 DECLARE@xXML SELECT@x=' <Employees> <Employeeid="1234"dept="IT"type="合同工"> <InfoNAME="dongsheng"SEX="男"QQ="5454545454"/> </Employee> <Employeeid="5656"dept="IT"type="临时工"> <InfoNAME="土豆"SEX="女"QQ="5345454554"/> </Employee> <Employeeid="3242"dept="市场"type="合同工"> <InfoNAME="choushuigou"SEX="女"QQ="54543545"/> </Employee> </Employees>' --查询dept为IT的人员信息 --方法1 SELECT C.value('@NAME[1]','VARCHAR(10)')ASNAME, C.value('@SEX[1]','VARCHAR(10)')ASSEX, C.value('@QQ[1]','VARCHAR(20)')ASQQ FROM@x.nodes('/Employees/Employee[@dept="IT"]/Info')T(C) /* NAMESEXQQ ---------------------------------------- dongsheng男5454545454 土豆女5345454554 */ --方法2 SELECT C.value('@NAME[1]','VARCHAR(10)')ASNAME, C.value('@SEX[1]','VARCHAR(10)')ASSEX, C.value('@QQ[1]','VARCHAR(20)')ASQQ FROM@x.nodes('//Employee[@dept="IT"]/*')T(C) /* NAMESEXQQ ---------------------------------------- dongsheng男5454545454 土豆女5345454554 */ --查询出IT部门type为Permanent的员工 SELECT C.value('@NAME[1]','VARCHAR(10)')ASNAME, C.value('@SEX[1]','VARCHAR(10)')ASSEX, C.value('@QQ[1]','VARCHAR(20)')ASQQ FROM@x.nodes('//Employee[@dept="IT"][@type="合同工"]/*')T(C) /* NAMESEXQQ ---------------------------------------- dongsheng男5454545454 */ --12.从XML变量中删除元素 DECLARE@xXML 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@xXML 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@ElementNameVARCHAR(20) SELECT@ElementName='NAME' SELECTc.value('.','VARCHAR(20)')ASNAME FROM@x.nodes('/Peoples/People/*[local-name()=sql:variable("@ElementName")]')T(C) /* NAME -------------------- dongsheng 土豆 choushuigou */ --20使用通配符读取元素值 --读取根元素的值 DECLARE@x1XML SELECT@x1='<People>dongsheng</People>' SELECT@x1.value('(/*/text())[1]','VARCHAR(20)')ASPeople--星号*代表一个元素 /* People -------------------- dongsheng */ --读取第二层元素的值 DECLARE@xXML SELECT@x=' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> <QQ>423545</QQ> </People>' SELECT @x.value('(/*/*/text())[1]','VARCHAR(20)')ASNAME /* NAME -------------------- dongsheng */ --读取第二个子元素的值 DECLARE@xXML SELECT@x=' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> <QQ>423545</QQ> </People>' SELECT @x.value('(/*/*/text())[2]','VARCHAR(20)')ASSEX /* SEX -------------------- 男 */ --读取所有第二层子元素值 DECLARE@xXML SELECT@x=' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> <QQ>423545</QQ> </People>' SELECT C.value('.','VARCHAR(20)')ASvalue FROM@x.nodes('/*/*')T(C) /* value -------------------- dongsheng 男 423545 */ --21.使用通配符读取元素名称 DECLARE@xXML SELECT@x='<People>dongsheng</People>' SELECT @x.value('local-name(/*[1])','VARCHAR(20)')ASElementName /* ElementName -------------------- People */ --读取根下第一个元素的名称和值 DECLARE@xXML SELECT@x=' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> </People>' SELECT @x.value('local-name((/*/*)[1])','VARCHAR(20)')ASElementName, @x.value('(/*/*/text())[1]','VARCHAR(20)')ASElementValue /* ElementNameElementValue ---------------------------------------- NAMEdongsheng */ --读取根下第二个元素的名称和值 DECLARE@xXML SELECT@x=' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> </People>' SELECT @x.value('local-name((/*/*)[2])','VARCHAR(20)')ASElementName, @x.value('(/*/*/text())[2]','VARCHAR(20)')ASElementValue /* ElementNameElementValue ---------------------------------------- SEX男 */ --读取根下所有的元素名称和值 DECLARE@xXML SELECT@x=' <People> <NAME>dongsheng</NAME> <SEX>男</SEX> </People>' SELECT C.value('local-name(.)','VARCHAR(20)')ASElementName, C.value('.','VARCHAR(20)')ASElementValue FROM@x.nodes('/*/*')T(C) /* ElementNameElementValue ---------------------------------------- NAMEdongsheng SEX男 */ ---22.查询元素数量 --如下Peoples根节点下有个People子节点。 DECLARE@xXML 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')ASChildren /* Children ----------- 3 */ --如下Peoples根节点下第一个子节点People下子节点的数量 SELECT@x.value('count(/Peoples/People[1]/*)','INT')ASChildren /* Children ----------- 2 */ --某些时候我们可能不知道根节点和子节点的名称,可以用通配符来代替。 SELECT@x.value('count(/*/*)','INT')ASChildrenOfRoot, @x.value('count(/*/*[1]/*)','INT')ASChildrenOfFirstChildElement /* ChildrenOfRootChildrenOfFirstChildElement ----------------------------------------- 32 */ --23.查询属性的数量 DECLARE@xXML SELECT@x=' <Employeesdept="IT"> <EmployeeNAME="dongsheng"SEX="男"QQ="5454545454"/> <EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/> </Employees>' --查询跟节点的属性数量 SELECT@x.value('count(/Employees/@*)','INT')ASAttributeCountOfRoot /* AttributeCountOfRoot -------------------- 1 */ --第一个Employee节点的属性数量 SELECT@x.value('count(/Employees/Employee[1]/@*)','INT')ASAttributeCountOfFirstElement /* AttributeCountOfFirstElement ---------------------------- 3 */ --第二个Employee节点的属性数量 SELECT@x.value('count(/Employees/Employee[2]/@*)','INT')ASAttributeCountOfSeconfElement /* AttributeCountOfSeconfElement ----------------------------- 4 */ --如果不清楚节点名称可以用*通配符代替 SELECT@x.value('count(/*/@*)','INT')ASAttributeCountOfRoot ,@x.value('count(/*/*[1]/@*)','INT')ASAttributeCountOfFirstElement ,@x.value('count(/*/*[2]/@*)','INT')ASAttributeCountOfSeconfElement /* AttributeCountOfRootAttributeCountOfFirstElementAttributeCountOfSeconfElement ----------------------------------------------------------------------------- 134 */ --返回没个节点的属性值 SELECTC.value('count(./@*)','INT')ASAttributeCount FROM@x.nodes('/*/*')T(C) /* AttributeCount -------------- 3 4 */ --24.返回给定位置的属性值或者名称 DECLARE@xXML SELECT@x=' <Employeesdept="IT"> <EmployeeNAME="dongsheng"SEX="男"QQ="5454545454"/> <EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/> </Employees>' --返回第一个Employee节点的第一个位置的属性值 SELECT@x.value('(/Employees/Employee[1]/@*[position()=1])[1]','VARCHAR(20)')ASAttValue /* AttValue -------------------- dongsheng */ --返回第二个Employee节点的第四个位置的属性值 SELECT@x.value('(/Employees/Employee[2]/@*[position()=4])[1]','VARCHAR(20)')ASAttValue /* AttValue -------------------- 13954697895 */ --返回第一个元素的第三个属性值 SELECT@x.value('local-name((/Employees/Employee[1]/@*[position()=3])[1])','VARCHAR(20)')ASAttName /* AttName -------------------- QQ */ --返回第二个元素的第四个属性值 SELECT@x.value('local-name((/Employees/Employee[2]/@*[position()=4])[1])','VARCHAR(20)')ASAttName /* AttName -------------------- TEL */ --通过变量传递位置返回属性值 DECLARE@EleposINT,@AttposINT SELECT@Elepos=2,@Attpos=3 SELECT@x.value('local-name((/Employees/Employee[sql:variable("@Elepos")]/@*[position()=sql:variable("@Attpos")])[1])','VARCHAR(20)')ASAttName /* AttName -------------------- QQ */ --25.判断是XML中否存在相应的属性 DECLARE@xXML SELECT@x='<EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/>' IF@x.exist('/Employee/@NAME')=1 SELECT'Exists'ASResult ELSE SELECT'Doesnotexist'ASResult /* Result ------ Exists */ --传递变量判断是否存在 DECLARE@xXML SELECT@x='<EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/>' DECLARE@attVARCHAR(20) SELECT@att='QQ' IF@x.exist('/Employee/@*[local-name()=sql:variable("@att")]')=1 SELECT'Exists'ASResult ELSE SELECT'Doesnotexist'ASResult /* Result ------ Exists */ --26.循环遍历元素的所有属性 DECLARE@xXML SELECT@x='<EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/>' DECLARE @cntINT, @totCntINT, @attNameVARCHAR(30), @attValueVARCHAR(30) SELECT @cnt=1, @totCnt=@x.value('count(/Employee/@*)','INT')--获得属性总数量 --loop WHILE@cnt<=@totCntBEGIN 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'AttributePosition:'+CAST(@cntASVARCHAR) PRINT'AttributeName:'+@attName PRINT'AttributeValue:'+@attValue PRINT'' --incrementthecountervariable SELECT@cnt=@cnt+1 END /* AttributePosition:1 AttributeName:NAME AttributeValue:土豆 AttributePosition:2 AttributeName:SEX AttributeValue:女 AttributePosition:3 AttributeName:QQ AttributeValue:5345454554 AttributePosition:4 AttributeName:TEL AttributeValue:13954697895 */ --27.返回指定位置的子元素 DECLARE@xXML SELECT@x=' <Employeesdept="IT"> <EmployeeNAME="dongsheng"SEX="男"QQ="5454545454"/> <EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/> </Employees>' SELECT@x.query('(/Employees/Employee)[1]') /* <EmployeeNAME="dongsheng"SEX="男"QQ="5454545454"/> */ SELECT@x.query('(/Employees/Employee)[position()=2]') /* <EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/> */ --通过变量获取指定位置的子元素 DECLARE@iINT SELECT@i=2 SELECT@x.query('(/Employees/Employee)[sql:variable("@i")]') --or SELECT@x.query('(/Employees/Employee)[position()=sql:variable("@i")]') /* <EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/> */ --28.循环遍历获得所有子元素 DECLARE@xXML SELECT@x=' <Employeesdept="IT"> <EmployeeNAME="dongsheng"SEX="男"QQ="5454545454"/> <EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/> </Employees>' DECLARE @cntINT, @totCntINT, @childXML --countervariables SELECT @cnt=1, @totCnt=@x.value('count(/Employees/Employee)','INT') --loop WHILE@cnt<=@totCntBEGIN SELECT @child=@x.query('/Employees/Employee[position()=sql:variable("@cnt")]') PRINT'ProcessingChildElement:'+CAST(@cntASVARCHAR) PRINT'Childelement:'+CAST(@childASVARCHAR(100)) PRINT'' --incremetthecountervariable SELECT@cnt=@cnt+1 END /* ProcessingChildElement:1 Childelement:<EmployeeNAME="dongsheng"SEX="男"QQ="5454545454"/> ProcessingChildElement:2 Childelement:<EmployeeNAME="土豆"SEX="女"QQ="5345454554"TEL="13954697895"/>
SQLServer中对XML数据的五种基本操作
1.xml.exist
输入为XQuery表达式,返回0,1或是Null。0表示不存在,1表示存在,Null表示输入为空
2.xml.value
输入为XQuery表达式,返回一个SQLServer标量值
3.xml.query
输入为XQuery表达式,返回一个SQLServerXML类型流
4.xml.nodes
输入为XQuery表达式,返回一个XML格式文档的一列行集
5.xml.modify
使用XQuery表达式对XML的节点进行insert,update和delete操作。
下面通过例子对上面的五种操作进行说明:
declare@XMLVarxml=' <catalog> <bookcategory="ITPro"> <title>WindowsStepByStep</title> <author>BillZack</author> <price>49.99</price> </book> <bookcategory="Developer"> <title>DevelopingADO.NET</title> <author>AndrewBrust</author> <price>39.93</price> </book> <bookcategory="ITPro"> <title>WindowsClusterServer</title> <author>StephenForte</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)')
结果集为:
WindowsStepByStepBillZack49.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')
结果集分别为:
<bookcategory="ITPro"> <title>WindowsStepByStep</title> <author>BillZack</author> <price>49.99</price> </book> <bookcategory="Developer"> <title>DevelopingADO.NET</title> <author>AndrewBrust</author> <price>39.93</price> </book> <bookcategory="ITPro"> <title>WindowsClusterServer</title> <author>StephenForte</author> <price>59.99</price> </book> <bookcategory="ITPro"> <title>WindowsStepByStep</title> <author>BillZack</author> <price>49.99</price> </book> <author>AndrewBrust</author>
4.xml.nodes
selectT.c.query('.')asresultfrom@XMLVar.nodes('/catalog/book')asT(c) selectT.c.query('title')asresultfrom@XMLVar.nodes('/catalog/book')asT(c)
结果集分别为:
<bookcategory="ITPro"><title>WindowsStepByStep</title><author>Bill………… <bookcategory="Developer"><title>DevelopingADO.NET</title><author>Andrew………… <bookcategory="ITPro"><title>WindowsClusterServer</title><author>Stephen………… <title>WindowsStepByStep</title> <title>DevelopingADO.NET</title> <title>WindowsClusterServer</title>
setARITHABORTon DECLARE@xXML SELECT@x='<Peoples> <People> <Email>1dongsheng@xxyy.com</Email> <Phone>678945546</Phone> <QQ>36575</QQ> <Addr>36575</Addr> </People> </Peoples>' --方法1 select1001aspeopleId,p.*FROM( SELECT C.value('local-name(.)','VARCHAR(20)')ASattrName, C.value('.','VARCHAR(20)')ASattrValue FROM@x.nodes('/*/*/*')T(C)--第三层 )asp /* 1001Email1dongsheng@xxyy.com 1001Phone678945546 1001QQ36575 1001Addr36575 */
/* 解析XML存储过程 */ ALTERPROCEDURE[dbo].[sp_ExportXml] @xxml, @layerstrnvarchar(max) AS DECLARE@sqlnvarchar(max) BEGIN setarithaborton set@sql='selectp.*FROM( SELECT C.value(''local-name(.)'',''VARCHAR(20)'')ASattrName, C.value(''.'',''VARCHAR(20)'')ASattrValue FROM@xmlParas.nodes('''+@layerstr+''')T(C) )asp' --print@sql EXECUTEsp_executesql@sql,N'@xmlParasasxml',@xmlParas=@x END
DECLARE@xXML SELECT@x= '<Peoples> <People> <Email>1dongsheng@xxyy.com</Email> <Phone>678945546</Phone> <QQ>36575</QQ> <Addr>36575</Addr> </People> </Peoples>' EXECUTEsp_ExportXml@x,'/*/*/*'
希望本文所述对大家SQLServer数据库程序设计有所帮助。