Need to get attribute name-value pairs for root element only, in T-SQL query

Question

I've created a table-valued function which returns a list of attribute name-value pairs, when fed an XML fragment, based on Ben Davis's excellent response here. It works, but returns a list of all the attribute name-value pairs in the entire fragment, when I'd like to restrict it to just those on the root element. How can I do this? Thank you, from an XQuery newbie.

INSERT INTO @attributeList
SELECT DISTINCT
    CAST(attribute.name.query('local-name(.)') AS VARCHAR(100)),
    attribute.name.value('.','NVARCHAR(MAX)')
FROM @xml.nodes('//@*') attribute(name)

ETA: As it turns out after some experimentation, the selector 'node()/@*' works. Thanks to those who helped.

I am using this in a simple data-migration application or data pump front-ending Sitecore. I've written a utility that can take POCO objects in .NET and get them into Sitecore, but now am constructed a migration and logging database. Source objects are stored in one place as XML. Thank you again.

Answer 1

This question has been answered in comments already so this is just a compilation with a bit of explanation.

For the experiments we will use this XML:

DECLARE @XML XML =
'<root root_attr="0">
   <leaf leaf_attr="1">one</leaf>
   <brunch brunch_attr="2">
     <leaf leaf_attr="3">three</leaf>
   </brunch>
</root>';

And we need to extract the list of the root element attributes: root_attr="0".

For the XPath reference we refer to MSDN XPath Syntax guide

So, "/" stands for "child" or "root node" if appears at the start of the pattern, "@" stands for "attribute", "*" stands for "any" and "." stands for "current context". Surely this should give us all the root attributes:

SELECT
    CAST(attribute.name.query('local-name(.)') AS VARCHAR(MAX)) As [Name],
    attribute.name.value('.','NVARCHAR(MAX)') As [Value]
FROM @XML.nodes('/@*') attribute(name);

Instead it gives an error: Top-level attribute nodes are not supported. There are two types of nodes in XML: <element>Element Value</element> and <element attribute="Attribute Value" />. So, /@* XPath is interpreted as any attribute for the root of XML, not of the root element. In fact that can be illustrated with:

SELECT
    CAST(attribute.name.query('local-name(.)') AS VARCHAR(MAX)) As [Name],
    attribute.name.value('.','NVARCHAR(MAX)') As [Value]
FROM @XML.nodes('/') attribute(name);

Returning:

Name Value
---- --------
     onethree

Which is an anonymous node representing the entire XML document. '.' XPath would give the same result.

Ok, so we need to specify any element at the root of the XML document. The syntax for that should have been "//" (child of anonymous root node = root element) should this expression not stand for "Recursive descent" (all children). Indeed

SELECT
    CAST(attribute.name.query('local-name(.)') AS VARCHAR(MAX)) As [Name],
    attribute.name.value('.','NVARCHAR(MAX)') As [Value]
FROM @XML.nodes('//@*') attribute(name);

Returns full list of attributes of all elements:

Name        Value
----------- --------
root_attr   0
leaf_attr   1
brunch_attr 2
leaf_attr   3

Ok, now we need a way to say in XPath "root" "element" instead of "rootelement" which is apparently a reserved word. One way is to squeeze in "any", the other - to specify that it should be "node()" unless of cause we know the actual name of the root element.

For the given XML those three are equal:

SELECT
    CAST(attribute.name.query('local-name(.)') AS VARCHAR(MAX)) As [Name],
    attribute.name.value('.','NVARCHAR(MAX)') As [Value]
FROM @XML.nodes('/*/@*') attribute(name);

SELECT
    CAST(attribute.name.query('local-name(.)') AS VARCHAR(MAX)) As [Name],
    attribute.name.value('.','NVARCHAR(MAX)') As [Value]
FROM @XML.nodes('/node()/@*') attribute(name);

SELECT
    CAST(attribute.name.query('local-name(.)') AS VARCHAR(MAX)) As [Name],
    attribute.name.value('.','NVARCHAR(MAX)') As [Value]
FROM @XML.nodes('/root/@*') attribute(name);

Returning:

Name      Value
--------- --------
root_attr 0

There we are. A bit of XPath tautology to work around the "//" reserved word.