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?

Answer 1

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.