How to convert nested XML into corresponding tables?

Question

I have a complex nested XML (generated from a C# entity graph), for example:

<Customers>
  <Customer>
    <Id>1</Id>
    <Number>12345</Number>
    <Addresses>
      <Address>
        <Id>100</Id>
        <Street>my street </street>
        <city>London</city>
      </Address>
      <Address>
        <Id>101</Id>
        <street>my street 2</street>
        <city>Berlin</city>
      </Address>
    </Addresses>
    <BankDetails>
      <BankDetail>
        <Id>222</Id>
        <Iban>DE8439834934939434333</Iban>
      </BankDetail>
      <BankDetail>
        <Id>228</Id>
        <Iban>UK1237921391239123213</Iban>
      </BankDetail>
    </BankDetails>
    <Orders>
      <Order>         
        <OrderLine>         
        </OrderLine>
      </Order>
    </Orders>
  </Customer>
</Customers>

Before saving the above XML data into the actual tables, I need to process it first. For this reason, I created corresponding table types. Each of these table types have an extra column (guid as ROWGUID) so that if I'm processing new data (not yet assigned primary key) I generate a unique key. I use this column to keep the relational integrity between different table types.

What is the SQL syntax to convert the above nested XML to their corresponding tables, keeping in mind that child records must reference the generated parent guid?

Your XML is invalid (wrong closing tag ``) and there are some `>` characters in the wrong place probably... (`*my street>*`) — Shnugo, Nov 02 '16 at 11:35
This question has already been answered at: http://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server — Penman, Nov 02 '16 at 11:38
A simple google query for "*parsing nested xml in sql*" turns up many good answers, including this one: http://stackoverflow.com/questions/29109342/parse-xml-with-multilevel-nesting-in-sql — RBarryYoung, Nov 02 '16 at 11:42
Even if such questions tend to look like *"Oh, it's a duplicate!"* I tend to answer them. All linked answers a helpfull, but out of my experience dealing with XML is very much depending on the actual XML. In this case there are multiple nested hierarchies, which none of the links covers. Thinking about people not used to this quite complex materia it is really difficult to *learn the rest* ... A solved example for the given XML is much better to learn from AFAIC... — Shnugo, Nov 02 '16 at 11:51
@Shnugo the apparent complete lack of effort by the OP was my concern. — RBarryYoung, Nov 02 '16 at 16:56

score 6 · Accepted Answer · answered Nov 02 '16 at 11:43

Try it like this:

DECLARE @xml XML=
N'<Customers>
  <Customer>
    <Id>1</Id>
    <AccountNumber>12345</AccountNumber>
    <Addresses>
      <Address>
        <Id>100</Id>
        <street>my street&gt;</street>
        <city>London</city>
      </Address>
      <Address>
        <Id>101</Id>
        <street>my street&gt;</street>
        <city>Berlin</city>
      </Address>
    </Addresses>
    <BankDetails>
      <BankDetail>
        <Id>222</Id>
        <Iban>DE8439834934939434333</Iban>
      </BankDetail>
      <BankDetail>
        <Id>228</Id>
        <Iban>UK1237921391239123213</Iban>
      </BankDetail>
    </BankDetails>
    <Orders>
      <Order>
        <OrderLine />
      </Order>
    </Orders>
  </Customer>
</Customers>';

--This query will create a table #tmpInsert with all the data

SELECT cust.value('Id[1]','int') AS CustomerID
      ,cust.value('AccountNumber[1]','int') AS CustomerAccountNumber
      ,addr.value('Id[1]','int') AS AddressId
      ,addr.value('street[1]','nvarchar(max)') AS AddressStreet
      ,addr.value('city[1]','nvarchar(max)') AS AddressCity
      ,bank.value('Id[1]','int') AS BankId
      ,bank.value('Iban[1]','nvarchar(max)') AS BankIban
      ,ord.value('OrderLine[1]','nvarchar(max)') AS OrderLine
INTO #tmpInsert
FROM @xml.nodes('/Customers/Customer') AS A(cust)
OUTER APPLY cust.nodes('Addresses/Address') AS B(addr)
OUTER APPLY cust.nodes('BankDetails/BankDetail') AS C(bank)
OUTER APPLY cust.nodes('Orders/Order') AS D(ord);

--Here you can check the content

SELECT * FROM #tmpInsert;

--Clean-Up

GO
DROP TABLE #tmpInsert

Once you've got all your data in the table, you can use simple DISTINCT, GROUP BY, if needed ROW_NUMBER() OVER(PARTITION BY ...) to select each set separately for the proper insert.

This works by placing nested values in columns of the one table. Is it possible to extract nested nodes into separate tables? And is it possible to avoid passing invididual columns in the SELECT part? — Ivan-Mark Debono, Nov 02 '16 at 11:53
Just use `INSERT INTO YourCustomerTable(Id,Number) SELECT DISTINCT CustomerId,CustomerAccountNumber FROM #tmpInsert;` and similiar for all the other nested sets. You can use `#tmpInsert` as source table for all of them. And no: I would really avoid to let away the *individual columns* At the moment your table is changed in any point a simple `INSERT INTO MyCustomerTabLE SELECT * FROM SomeWhere` will - for sure! - lead into troubles in future! — Shnugo, Nov 02 '16 at 11:57

How to convert nested XML into corresponding tables?

1 Answers1