For this xml (in a SQL 2005 XML column):
<doc>
<a>1</a>
<b ba="1" bb="2" bc="3" />
<c bd="3"/>
<doc>
I'd like to be able to retrieve the names of the attributes (ba, bb, bc, bd) rather than the values inside SQL Server 2005. Well, XPath certainly allows this with name() but SQL doesn't support that. This is my chief complaint with using XML in SQL; you have to figure out which parts of the XML/Xpath/XQuery spec are in there.
The only way I can think of to do this is to build a CLR proc that loads the XML into an XML Document (iirc) and runs the XPath to extract the names of the nodes. I'm open to suggestions here.
DECLARE @xml as xml
SET @xml =
'<doc>
<a>1</a>
<b ba="1" bb="2" bc="3" />
<c bd="3"/>
</doc>'
SELECT DISTINCT
CAST(Attribute.Name.query('local-name(.)') AS VARCHAR(100)) Attribute,
Attribute.Name.value('.','VARCHAR(100)') Value
FROM @xml.nodes('//@*') Attribute(Name)
Returns:
Attribute Value
ba 1
bb 2
bc 3
bd 3