SQL Server 2005 XML 操作总结（八）属性操作——删除、遍历操作

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

--删除title的lang="en"且 price>35的所有book的category属性
set @data.modify('delete bookstore/book[./title[@lang="en"] or
 price>35 ]/@category')
  /*output:
<book>
    <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>
    <title lang="cn">Learning XML</title>
    <author>Erik T. Ray</author>
    <year>2000</year>
    <price>39.95</price>
  </book>*/

======循环遍历元素的所有属性=======
--循环变量第一个book节点的所有属性
DECLARE 
    @cnt INT, 
    @totCnt INT,
    @attName VARCHAR(30),
    @attValue VARCHAR(30) 
SELECT 
    @cnt = 1,
    @totCnt = @data.value('count(/bookstore/book[1]/@*)','INT')--获得属性总数量
-- loop
WHILE @cnt <= @totCnt BEGIN
    SELECT
        @attName = @data.value(
          'local-name((/bookstore/book/@*[position()=sql:variable("@cnt")])[1])',
            'VARCHAR(30)'),
        @attValue = @data.value(
            '(/bookstore/book/@*[position()=sql:variable("@cnt")])[1]',
            'VARCHAR(30)')
    PRINT 'Attribute Position: ' + CAST(@cnt AS VARCHAR)
    PRINT 'Attribute Name: ' + @attName
    PRINT 'Attribute Value: ' + @attValue
    PRINT ''
    -- increment the counter variable
    SELECT @cnt = @cnt + 1
END
/*output
Attribute Position: 1
Attribute Name: category
Attribute Value: COOKING
*/