How to get list of XML attributes in a XML node

Question

I have scenario where the XML data that I get in a sql table could have dynamic list of attributes related to a specific element. For example, XML could be

<XML><row ID = 1 x1="x1value" x2 = "x2value" /></XML>
<XML><row ID = 10 X3 = "x3value" x10 = "x10value" /></XML>
<XML><row ID = 5 x1="x1value2" x10 = "x10value2" /></XML>

I have to treat each of the XML data separately, and dynamically shred and fetch values of each attributes using MSSQL query like a key value pair into a temp table or flatten it into a relational model like

SELECT x1, x2 INTO #temp FROM xml...

All the examples i studied shows the way to extract if you know the attributes present in the XML. In my example, I don't know what attributes are present in the element.

Any help would be greatly appreciated :)

Answer 1

Try this,

DECLARE @XML as xml

SET @XML = '<XML><row ID="1" x1="x1value" x2="x2value" /></XML>'

SELECT 
    k.value('@ID','BIGINT') AS ID,
    b.value('local-name(.)','VARCHAR(50)') AS ColumnName,  -- get attribute name
    b.value('.','VARCHAR(MAX)') AS Value  -- get attribute value
FROM @XML.nodes('/XML/row') p(k)
CROSS APPLY k.nodes('@*') a(b)

Output:

ID  ColumnName   Value    
--- ------------ -----------
1   ID           1
1   x1           x1value
1   x2           x2value

Hope this helps