SQL Server 2005 XML 操作总结（转）

转载网上的，方便以后查找

 

后续文章中的操作全部都针对该示例

 

declare @data xml
set @data='
<bookstore>
<book category="COOKING" >
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="CHILDREN">
<title lang="jp">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="WEB">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<author>Kurt Cagle</author>
<author>James Linn</author>
<author>Vaidyanathan Nagarajan</author>
<year>2003</year>
<price>49.99</price>
</book>
<book category="WEB">
<title lang="cn">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>
'

 

节点查询操作

 

•  一般查询

--文档
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:
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
*/
--获取最后一个book节点
select @data.query('//book[last()]')
/*output:
<book category="WEB">
......
</book>*/ 
--获取price>35的所有book节点
select @data.query('//book[price>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>35的所有book节点
select @data.query('//book[./title[@lang="en"] or price>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>35的第一book的(第一个)title
select @data.query('//book[./title[@lang="en"] and price>35 ]')

　　　　　　　　　　　　　　.value('(book/title)[1]','varchar(max)')
/*output:
XQuery Kick Start
*/
--等价于“获取title的lang="en"且 price>35的第一book的(第一个)title”
select @data.value('(//book[./title[@lang="en"] and price>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[. << (/bookstore/book[@category="WEB"])[1]])[last()]')
/*output:
<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[. >>
(/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
*/

 

属性查询操作

 

 

========一般查询=========
--获得第一个book节点的category属性值
select @data.value('(/bookstore/book/@category)[1]', 'nvarchar(max)')
/*output:
COOKING
*/
--获取title的lang="en"且 price>35的第一book的(第一个)title的lang属性
select @data.value('((//book[@category="WEB" and 
price>35 ]/title)[1]/@lang)[1]','varchar(max)')
/*output:
en
*/
--获取第一本书的title
select @data.value('(/bookstore/book[position()=1]/title)[1]','varchar(max)') 
/*output:
Everyday Italian
*/


========根据属性名称查询========
--获取第一个book节点的名为category的属性值
DECLARE @att VARCHAR(20)
SELECT @att = 'category'
select @data.value('(/bookstore/book/@*[local-name()=sql:variable("@att")])[1]','VARCHAR(20)')
/*output:
COOKING
*/
========映射为表结构查询========

-获取每本书的第一个author
select Tab.Col.value('author[1]','varchar(max)') as author
    from @data.nodes('//book')as Tab(Col)


 


--获取所有book的所有信息
select
T.C.value('title[1]','varchar(max)') as title,
T.C.value('year[1]','int') as year,
T.C.value('price[1]','float') as price,
T.C.value('author[1]','varchar(max)') as author1,
T.C.value('author[2]','varchar(max)') as author2,
T.C.value('author[3]','varchar(max)') as author3,
T.C.value('author[4]','varchar(max)') as author4
from @data.nodes('//book') as T(C)





--获取不是日语(lang!="jp")且价格大于35的书的所有信息
select
T.C.value('title[1]','varchar(max)') as title,
T.C.value('year[1]','int') as year,
T.C.value('price[1]','float') as price,
T.C.value('author[1]','varchar(max)') as author1,
T.C.value('author[2]','varchar(max)') as author2,
T.C.value('author[3]','varchar(max)') as author3,
T.C.value('author[4]','varchar(max)') as author4
from @data.nodes('//book[./title[@lang!="jp"] and price>35 ]') as T(C)



 
 
========模糊查询=========



--查询第一个book节点的第一个属性值
SELECT @data.value('(/bookstore/book[1]/@*[position()=1])[1]','VARCHAR(20)')
/*output:
COOKING
*/


 
=========查询属性数量=========
--查询第一个book节点的属性数量
SELECT @data.value('count(/bookstore/book[1]/@*)','INT')
/*output:
1*/

 

修改、删除、移动、遍历

 

========修改操作======

--将category="WEB"的第一个book节点的year值改为2000
set @data.modify('replace value of
(/bookstore/book[@category="WEB"]/year/text())[1] with "2000"')
/*output:
<book category="WEB">
<title lang="cn">Learning XML</title>
<author>Erik T. Ray</author>
<year>2000</year>
<price>39.95</price>
</book>
*/
--替换第一个book节点的author的内容为“替换内容”
set @data.modify('replace value of(/bookstore/book[1]/author[1]/text())[1] with ("替换内容")')
/*output:
<book category="COOKING">
<title lang="en">Everyday Italian</title>
<author>替换内容</author>
<year>2005</year>
<price>30.00</price>
</book>*/

 ========删除操作========

--删除title的@lang="en"的所有book节点
set @data.modify('delete /bookstore/book[./title[@lang="en"]]')
/*output:
<bookstore>
  <book>
    <title lang="jp">Harry Potter</title>
    <author>J K. Rowling</author>
    <year>2005</year>
    <price>29.99</price>
  </book>
  <book category="WEB">
    <title lang="cn">Learning XML</title>
    <author>Erik T. Ray</author>
    <year>2003</year>
    <price>39.95</price>
  </book>
</bookstore>
*/
--删除第一个book节点的author的内容
set @data.modify('delete /bookstore[1]/book[1]/author[1]/text()')
/*output:
  <book category="COOKING">
    <title lang="en">Everyday Italian</title>
    <author />
    <year>2005</year>
    <price>30.00</price>
  </book>
*/
========移动操作=========

--title="Harry Potter"的book节点在同级中上移一层
set @data.modify('insert (/bookstore/book[title="Harry Potter"]) 
before (/bookstore/book[. << (/bookstore/book[title="Harry Potter"])[1]])
[last()]')
SET @data.modify ('delete /bookstore/book[title="Harry Potter"] 
[. is (/bookstore/book[title="Harry Potter"])[last()]]')
/*output:
<book category="CHILDREN">
......
</book>
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>*/ 
--title="Harry Potter"的book节点在同级中下移一层
set @data.modify('insert (/bookstore/book[title="Harry Potter"]) 
before (/bookstore/book[. >> (/bookstore/book[title="Harry Potter"])[1]])
[last()]')
SET @data.modify ('delete /bookstore/book[title="Harry Potter"][1] ')
/*output:
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>*/ 
--title="Harry Potter"的book节点移到category为COOKING的book节点前
set @data.modify('insert (/bookstore/book[title="Harry Potter"]) 
before (/bookstore/book[@category="COOKING"])[1]')
SET @data.modify ('delete /bookstore/book[title="Harry Potter"] [2]')
/*output:
<book category="CHILDREN">
......
</book>
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>*/ 
--title="Harry Potter"的book节点移到categroy为WEB的第一个book节点后
set @data.modify('insert (/bookstore/book[title="Harry Potter"]) 
after (/bookstore/book[@category="WEB"])[1] ')
SET @data.modify ('delete /bookstore/book[title="Harry Potter"][1] ')
/*output:
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>*/ 

========循环遍历所有元素=========

--循环所有book节点
DECLARE 
    @cnt INT, 
    @totCnt INT,
    @child XML
-- counter variables
SELECT 
    @cnt = 1,
    @totCnt = @data.value('count(/bookstore/book)','INT')
-- loop
WHILE @cnt <= @totCnt BEGIN
    SELECT
        @child = @data.query('/bookstore/book[position()=sql:variable("@cnt")]')
    PRINT 'Processing Child Element: ' + CAST(@cnt AS VARCHAR)
    PRINT 'Child element:  ' + CAST(@child AS VARCHAR(max))
    PRINT ''
    -- incremet the counter variable
    SELECT @cnt = @cnt + 1
END
/*output
Processing Child Element: 1
Child element:  <book category="COOKING">......</book>

Processing Child Element: 2
Child element:  <book><title lang="jp">......</book>


Processing Child Element: 3
Child element:  <book category="WEB">......</book>

Processing Child Element: 4
Child element:  <book category="WEB">......</book>