Consider the below XML document
Now check the below code
SELECT t.u.value(‘../OrderID[1]’,’int’) AS OrderID,
t.u.value(‘local-name(.)’,’varchar(100)’) AS AttribName,
t.u.value(‘.’,’varchar(100)’) AS AttribValue
FROM @X.nodes(‘Orders/Order/*’) t(u)
UNION ALL
SELECT t.u.value(‘../OrderID[1]’,’int’),
m.n.value(‘local-name(.)’,’varchar(100)’) ,
m.n.value(‘.’,’varchar(100)’)
FROM @X.nodes(‘Orders/Order/*’) t(u)
CROSS APPLY t.u.nodes(‘./@*’) m(n)
ORDER BY OrderID
This code makes use of nodes() function to shred the data from nodes of XML document. This is then cross applied to the nodes collection to look for attributes using @* argument which returns all the attributes within the XML document to give us the below result.
If you want attributes side by side along with the node details, you can tweak the code as below
SELECT t.u.value(‘../OrderID[1]’,’int’) AS OrderID,
t.u.value(‘local-name(.)’,’varchar(100)’) AS NodeName,
t.u.value(‘.’,’varchar(100)’) AS NodeValue,
m.n.value(‘local-name(.)’,’varchar(100)’) AS AttribName,
m.n.value(‘.’,’varchar(100)’) AS AttribValue
FROM @X.nodes(‘Orders/Order/*’) t(u)
OUTER APPLY t.u.nodes(‘./@*’) m(n)
ORDER BY OrderID
And this gives you the below result format