Shred data as well as metadata from XML document

Quite often I’ve come across requirements where we’ve the data coming in XML nodes and requiring us to create a table using them. The below code gives us a method to shred the data from XML document. The code shreds the node and attribute names along with values from the XML document.

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