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
