SQL Server 2005 XML 操作总结（五）元素（节点）操作——修改、删除、移动、遍历操作...

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

--将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>

转载于:https://www.cnblogs.com/FlyingPig-Nannan/archive/2011/03/04/1971128.html