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