T_SQL 遍历 XML

declare @data XML;
set @data='<bookstore>
 <book category="COOKING" isbn="3434343" > 
  <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>';

--遍历节点
DECLARE @count INT, @totalCount INT, @child XML, @attributeName VARCHAR(30), @attributeValue VARCHAR(30);
SET @count=1;
SET @totalCount=@data.value('count(/bookstore/book)','INT');
PRINT ''
While @count<=@totalCount
BEGIN
 SET @child=@data.query('/bookstore/book[position()=sql:variable("@count")]')
 PRINT 'Processing Child Element:' + CAST(@count AS VARCHAR)
 PRINT 'Child element: ' + CAST(@child AS VARCHAR(max))
 PRINT ''
 SET @count=@count+1
END

--遍历属性
SET @count=1;
SET @totalCount=@data.value('count(/bookstore/book[1]/@*)','INT');
WHILE @count<=@totalCount
BEGIN
 SET @attributeName = @data.value(
  'local-name((/bookstore/book/@*[position()=sql:variable("@count")])[1])'
  , 'VARCHAR(30)')      
    SET @attributeValue = @data.value(
  '(/bookstore/book/@*[position()=sql:variable("@count")])[1]'
  ,'VARCHAR(30)') 
    PRINT 'Attribute Position: ' + CAST(@count AS VARCHAR)
    PRINT 'Attribute Name: ' + @attributeName   
    PRINT 'Attribute Value: ' + @attributeValue  
    PRINT ''   
    SET @count = @count + 1
END

转载于:https://www.cnblogs.com/Tim-Yi/archive/2011/10/25/2223926.html