T_SQL 遍历 XML

最新推荐文章于 2025-03-20 22:28:11 发布

转载最新推荐文章于 2025-03-20 22:28:11 发布 · 280 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Tim-Yi/archive/2011/10/25/2223926.html

博客展示了XML数据的操作示例，通过SQL代码定义XML数据，包含书籍信息。接着进行节点遍历，输出每个书籍子元素内容；还进行属性遍历，输出属性位置、名称和值，为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