XML Fragment @XMLData XML

<table numcols="9" tag="TEST_TBL">
<Group fmt="center">
<data tag="ProductId">
<string>1234</string>
</data>
<data tag="ProductName">
<string>Horses</string>
</data>
.....
I am using the following syntax and it works great returning all node names
and node values. The names and values come in unknown which is why I am using
the wildcard method. The question is how do I return, in the same query, the
tag attribute?

SELECT NodeName, NodeValue
FROM
(SELECT Item.value(N'fn:local-name(.[1])', N'nvarchar(1000)') AS NodeName,
Cast(Item.query(N'./text()') as Varchar(200)) AS NodeValue
FROM @XMLData.nodes(N'//*') X(Item)
) sub

Returns
String, 1234
String, Horses

--
RJ Roberts
DB Architect/Developer

Try this:

SELECT NodeName, NodeValue, NodeValue2, tag
FROM
(
SELECT
Item.value(N'fn:local-name(.[1])', N'nvarchar(1000)') AS NodeName,
Cast(Item.query(N'./text()') as Varchar(200)) AS NodeValue,
Item.value(N'./text()[1]', 'Varchar(200)') AS NodeValue2,
Item.value(N'@tag[1]', 'Varchar(200)') AS tag

FROM @XMLData.nodes(N'//*') X(Item)
) sub


Come over to this group for all your future SQL Server XML needs:
http://social.msdn.microsoft.com/For...sqlxml/threads

HTH
wBob