Shredding XML

To turn the XML into data, we have a number of steps.

First, let us create some XML to play with:

SELECT cust.CustomerID, 
       cust.CustomerType, 
       [Order].InvoiceID, 
       [Order].OrderDate, 
       OrderDetail.ProductID, 
       OrderDetail.Quantity 
FROM Customer AS cust 
INNER JOIN SalesInvoice AS [Order] 
           ON Cust.CustomerID = [Order].CustomerID 
INNER JOIN SalesInvoiceDetail AS OrderDetail 
           ON [Order].InvoiceID = OrderDetail.InvoiceID 
WHERE Cust.CustomerID = 2
for xml auto

Now, we take that XML and load it into memory

declare @doc xml

set @doc='<cust CustomerID="2" CustomerType="Consumer">
  <Order InvoiceID="943" OrderDate="2008-02-07T02:45:03.840">
    <OrderDetail ProductID="72" Quantity="4" />
  </Order>
</cust>'

declare @hdoc int
exec sp_xml_prepareDocument @hdoc output, @doc

Then we can work with the xml.
select * from openxml(@hdoc, '/cust/Order/OrderDetail')

id parntid nodetpe localname prefix nameuri datatyp prev text

7 4 1 OrderDetail NULL NULL NULL NULL NULL

8 7 2 ProductID NULL NULL NULL NULL NULL

10 8 3 #text NULL NULL NULL NULL 72

9 7 2 Quantity NULL NULL NULL NULL NULL

11 9 3 #text NULL NULL NULL NULL 4

While all the data is there, this is still a little fugly.

Adding one line to the bottom of the query makes it much cleaner:

select * 
from openxml(@hdoc, '/cust/Order/OrderDetail')
with (ProductID INT, Quantity int)

ProductID Quantity

72       4

Now life is better.

To reclaim the memory used when loading the XML, issue the command:

exec sp_xml_RemoveDocument 1