SQL Server 2005 XML 操作总结（三）元素（节点）操作——查询操作

• 一般查询

--文档
select @data
/*output:
<bookstore>
<book category="COOKING">
......
</book>
</bookstore>
*/
--任意级别是否存在price节点
select @data.exist('//price')
/*output:
1
*/
--获取所有book节点
select @data.query('//book')
/*output:
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>*/
--获取第一个book节点
select @data.query('//book[1]')
/*output:
<book category="COOKING">
......
</book>
*/
--获取前两个book节点
select @data.query('//book[position()<=2]')
/*output:
&lt;book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
*/
--获取最后一个book节点
select @data.query('//book[last()]')
/*output:
<book category="WEB">
......
</book>*/
--获取price&gt;35的所有book节点
select @data.query('//book[price&gt;35]')
/*output:
<book category="WEB">
<title lang="en">XQuery Kick Start</title>
……
</book>
<book category="WEB">
<title lang="cn">Learning XML</title>
……
</book>
*/
--获取category="WEB"的所有book节点
select @data.query('//book[@category="WEB"]')
/*output:
<book category="WEB">
<title lang="en">XQuery Kick Start</title>
……
</book>
<book category="WEB">
<title lang="cn">Learning XML</title>
……
</book>
*/
--获取含category属性的所有book节点
select @data.query('/bookstore/book[@category]')
/*output:
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>*/
--获取book节点下title的lang="en"的所有title节点
select @data.query('//book/title[@lang="en"]')
/*output:
<title lang="en">Everyday Italian</title>
<title lang="en">XQuery Kick Start</title>
*/
--获取title的lang="en"且 price&gt;35的所有book节点
select @data.query('//book[./title[@lang="en"] or price&gt;35 ]')
/*output:
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>*/
--获取第一个book元素的title元素的值
select @data.value('(/bookstore/book/title)[1]', 'nvarchar(max)')
/*output:
Everyday Italian
*/
--获取title的lang="en"且 price&gt;35的第一book的(第一个)title
select @data.query('//book[./title[@lang="en"] and price&gt;35 ]')

　　　　　　　　　　　　　　.value('(book/title)[1]','varchar(max)')
/*output:
XQuery Kick Start
*/
--等价于“获取title的lang="en"且 price&gt;35的第一book的(第一个)title”
select @data.value('(//book[./title[@lang="en"] and price&gt;35 ]/title)[1]','varchar(max)')
/*output:
XQuery Kick Start
*/

复制代码

• 根据节点名称查询

--查找第一个book节点的第一个名为title的节点的值
DECLARE @ElementName VARCHAR(20)
SELECT @ElementName = 'title'
select @data.value('(/bookstore/book/*[local-name()=

　　　　　　　　　　　　sql:variable("@ElementName")])[1]','varchar(30)')
/*output:
Everyday Italian
*/

复制代码

• 映射为表结构查询

--将book元素映射到数据表book列
select Tab.Col.query('.') as book from @data.nodes('/bookstore/book')Tab(Col); --查询price元素的位置和值
SELECT a.number as position,b.price
FROM master.dbo.spt_values A
CROSS APPLY (
SELECT C.value('price[1]','float') as price
FROM @data.nodes('/bookstore/book[position()=sql:column("number")]')T(C)) b
WHERE A.type='P'

复制代码

• 模糊查询

--获取所有包含lang属性的节点
select @data.query('//*[@lang]')
/*output:
<title lang="en">Everyday Italian</title>
<title lang="jp">Harry Potter</title>
<title lang="en">XQuery Kick Start</title>
<title lang="cn">Learning XML</title>*/
--获取所有book节点
select @data.query('/bookstore/*')
/*output:
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>*/
--获取所有节点
select @data.query('//*')
/*output:
<bookstore>
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>
</bookstore>
*/
--获取所有包含属性的title节点
select @data.query('//title[@*]')
/*output:
<title lang="en">Everyday Italian</title>
<title lang="jp">Harry Potter</title>
<title lang="en">XQuery Kick Start</title>
<title lang="cn">Learning XML</title>*/
--读取根节点的名称
SELECT @data.value('local-name(/*[1])','VARCHAR(20)') AS ElementName
/*output
bookstore
*/
--读取第三级上第一个节点的名称和值
SELECT @data.value('local-name((/*/*/*)[1])','VARCHAR(20)') AS ElementName,
@data.value('(/*/*/*/text())[1]','VARCHAR(20)') AS ElementValue
/*output
title Everyday Italian
*/
--读取第二级上第一个节点下属节点的名称和值
SELECT
C.value('local-name(.)','VARCHAR(200)') AS ElementName,
C.value('.','VARCHAR(200)') AS ElementValue
FROM @data.nodes('/*/*[1]/*') T(C)

 

复制代码

• 相邻节点查询

--获取第一个category=“WEB”的book节点的前一个book节点
select @data.query('(/bookstore/book[. <&lt; (/bookstore/book[@category="WEB"])[1]])[last()]')
/*output:
&lt;book>
<title lang="jp">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>*/
--获取第一个category=“WEB”的book节点的前一个book节点
select @data.query('(/bookstore/book[. &gt;&gt;
(/bookstore/book[@category="WEB"])[1]])[1]')
/*output:
<book category="WEB">
<title lang="cn">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>*/

复制代码

• 节点数目查询

--获得book节点的数量
SELECT @data.value('count(/bookstore/book)','INT')
/*output:
4
*/

复制代码

转载于:https://blog.51cto.com/shenlan/807514