Parse xml analysis into denormalized table

Question

How to parse embedded XML in sql server into one table. Given that RowGuid is unique to every customer

eg,

I want to parse this xml in one table, which will be denormalized and will contain from one to many relationships. Given that each nesting has a primary key for business.

<Customers> <Customer> <Type xsi:nil="true" /> <RowGuid>FEFF32BC-1DAB-4F8A-80F0-CFE293C0BEC4</RowGuid> <AccountId>0</AccountId> <AccountNumber>bdb8eb51-d</AccountNumber> <AccountTransactions> <AccountTransaction> <PaymentDate>2012-09-13 22:19:58</PaymentDate> <Balance>500</Balance> </AccountTransaction> <AccountTransaction> <PaymentDate>2012-09-13 22:19:58</PaymentDate> <Balance>500</Balance> </AccountTransaction> </AccountTransactions> <Addresses> <Address> <city>DELHI</city> </Address> <Address> <city>MUMBAI</city> </Address> </Addresses> </Customer> <Customer> <Type xsi:nil="true" /> <RowGuid>C3D4772E-1DAB-4F8A-80F0-CFE293C0BEC4</RowGuid> <AccountId>0</AccountId> <AccountNumber>bdb8eb51-d</AccountNumber> <AccountTransactions> <AccountTransaction> <PaymentDate>2012-09-13 22:19:58</PaymentDate> <Balance>500</Balance> </AccountTransaction> <AccountTransaction> <PaymentDate>2012-09-13 22:19:58</PaymentDate> <Balance>500</Balance> </AccountTransaction> </AccountTransactions> </Customer> 

Answer 1

If the table does not need normalization, you can do LEFT JOIN . I also added a namespace to the Customers element, this is necessary because of xsi:nil="true" . Try:

 DECLARE @xml XML = '<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Customer> <Type xsi:nil="true" /> <RowGuid>FEFF32BC-1DAB-4F8A-80F0-CFE293C0BEC4</RowGuid> <AccountId>0</AccountId> <AccountNumber>bdb8eb51-d</AccountNumber> <AccountTransactions> <AccountTransaction> <PaymentDate>2012-09-13 22:19:58</PaymentDate> <Balance>500</Balance> </AccountTransaction> <AccountTransaction> <PaymentDate>2012-09-13 22:19:58</PaymentDate> <Balance>500</Balance> </AccountTransaction> </AccountTransactions> <Addresses> <Address> <city>DELHI</city> </Address> <Address> <city>MUMBAI</city> </Address> </Addresses> </Customer> <Customer> <Type xsi:nil="true" /> <RowGuid>C3D4772E-1DAB-4F8A-80F0-CFE293C0BEC4</RowGuid> <AccountId>0</AccountId> <AccountNumber>bdb8eb51-d</AccountNumber> <AccountTransactions> <AccountTransaction> <PaymentDate>2012-09-13 22:19:58</PaymentDate> <Balance>500</Balance> </AccountTransaction> <AccountTransaction> <PaymentDate>2012-09-13 22:19:58</PaymentDate> <Balance>500</Balance> </AccountTransaction> </AccountTransactions> </Customer> </Customers>' SELECT a.[Type], a.RowGuid, a.AccountId, a.AccountNumber, b.PaymentDate, b.Balance, c.[Address] FROM ( SELECT Customer.value('Type[1]', 'VARCHAR(500)') [Type], Customer.value('RowGuid[1]', 'UNIQUEIDENTIFIER') RowGuid, Customer.value('AccountId[1]', 'INT') AccountId, Customer.value('AccountNumber[1]', 'VARCHAR(500)') AccountNumber FROM @xml.nodes('/Customers/Customer') tbl(Customer) ) a LEFT JOIN ( SELECT AccountTransaction.value('PaymentDate[1]', 'DATETIME') PaymentDate, AccountTransaction.value('Balance[1]', 'DECIMAL(20, 2)') Balance, AccountTransaction.value('../../RowGuid[1]', 'UNIQUEIDENTIFIER') RowGuid FROM @xml.nodes('/Customers/Customer/AccountTransactions/AccountTransaction') tbl(AccountTransaction) ) b ON a.RowGuid = b.RowGuid LEFT JOIN ( SELECT Address.value('city[1]', 'VARCHAR(500)') [Address], Address.value('../../RowGuid[1]', 'UNIQUEIDENTIFIER') RowGuid FROM @xml.nodes('/Customers/Customer/Addresses/Address') tbl(Address) ) c ON a.RowGuid = c.RowGuid 

UPDATE:

Due to the high cost of requesting the first version of this request (one that uses XML data type methods), I created another version that uses OPENXML methods instead of nodes and value . There is a big difference in cost in favor of the OPENXML approach:

 DECLARE @handle INT CREATE TABLE #Customer (Type VARCHAR(500), RowGuid UNIQUEIDENTIFIER, AccountId INT, AccountNumber VARCHAR(500) ) CREATE TABLE #AccountTransaction ( PaymentDate DATETIME, Balance DECIMAL(20, 2), RowGuid UNIQUEIDENTIFIER ) CREATE TABLE #Address ( City VARCHAR(500), RowGuid UNIQUEIDENTIFIER ) EXEC sp_xml_preparedocument @handle OUTPUT, @xml INSERT #Customer SELECT * FROM OPENXML(@handle, '/Customers/Customer', 2) WITH ( Type VARCHAR(500), RowGuid UNIQUEIDENTIFIER, AccountId INT, AccountNumber VARCHAR(500) ) INSERT #AccountTransaction SELECT * FROM OPENXML(@handle, '/Customers/Customer/AccountTransactions/AccountTransaction', 2) WITH ( PaymentDate DATETIME, Balance DECIMAL(20, 2), RowGuid UNIQUEIDENTIFIER '../../RowGuid[1]' ) INSERT #Address SELECT * FROM OPENXML(@handle, '/Customers/Customer/Addresses/Address', 2) WITH ( city VARCHAR(500), RowGuid UNIQUEIDENTIFIER '../../RowGuid[1]' ) SELECT a.*, b.PaymentDate, b.Balance, c.City FROM #Customer a LEFT JOIN #AccountTransaction b ON b.RowGuid = a.RowGuid LEFT JOIN #Address c ON c.RowGuid = a.RowGuid EXEC sp_xml_removedocument @handle