Get The Names Of Attributes From An Element In A Sql Xml Column

For this xml (in a SQL 2005 XML column): 1 I'd like to be able to retrieve the

Solution 1:

DECLARE@xmlas xml

SET@xml='<doc>
 <a>1</a>
 <b ba="1" bb="2" bc="3" />
 <c bd="3"/>
</doc>'SELECTDISTINCTCAST(Attribute.Name.query('local-name(.)') ASVARCHAR(100)) Attribute,
 Attribute.Name.value('.','VARCHAR(100)') ValueFROM@xml.nodes('//@*') Attribute(Name)

Returns:

Attribute Value

ba 1

bb 2

bc 3

bd 3

Solution 2:

DECLARE@xmlas xml
DECLARE@pathasvarchar(max)
DECLARE@indexint, @countintSET@xml='<doc>
 <a>1</a>
 <b ba="1" bb="2" bc="3" />
 <c bd="3"/>
</doc>'SELECT@index=1SET@count=@xml.query('count(/doc/b/@*)').value('.','int')

WHILE @index<=@countBEGINSELECT@xml.value('local-name((/doc/b/@*[sql:variable("@index")])[1])', 'varchar(max)')
    SET@index=@index+1END

for element 'b'

it returns

• ba
• bb
• bc

You can build a loop to get attributes for each element in the xml.

BTW The XML in your sample should be closed at closing doc tag.

Solution 3:

Declare @xml Xml = '<doc><a>1</a><bba="1"bb="2"bc="3" /><cbd="3"/></doc>'

Select n.value('local-name(.)', 'varchar(max)')  from @xml.nodes('/doc/*/@*') a(n)

Returns ba bb bc bd