Some time ago I demonstrated how to use XQuery to map a hierarchical XML document into parent- child relational tables. You can find that article here. The other way around, (to create create XML with T-SQL), is a lot easier to do.
We’ll use the same data as in the XQuery example. Use the following script to create the database.
To fill the database with some sample data, run the following T-SQL:
INSERT dbo.Invoices (InvoiceNumber, InvoiceDate, XMLPosition) VALUES (991001, '2013-12-19', 1), (991002, '2013-12-20', 2) INSERT dbo.InvoiceRows (InvoiceId, Product, Amount) VALUES (1, 'PROD1234', 1000), (1, 'PROD5678', 100), (2, 'PROD1234', 2000)
The data describes invoices (parent) with matching invoice rows:
From those tables, the goal is to create the following XML document:
<Invoices> <Invoice> <InvoiceNumber>991001</InvoiceNumber> <InvoiceDate>2013-12-19</InvoiceDate> <InvoiceRows> <InvoiceRow> <Product>PROD1234</Product> <Amount>1000</Amount> </InvoiceRow> <InvoiceRow> <Product>PROD5678</Product> <Amount>100</Amount> </InvoiceRow> </InvoiceRows> </Invoice> <Invoice> <InvoiceNumber>991002</InvoiceNumber> <InvoiceDate>2013-12-20</InvoiceDate> <InvoiceRows> <InvoiceRow> <Product>PROD1234</Product> <Amount>2000</Amount> </InvoiceRow> </InvoiceRows> </Invoice> </Invoices>
The XML document above can be created by using the FOR XML clause, which is used when creating XML with T-SQL:
SELECT I.InvoiceNumber, I.InvoiceDate, ( SELECT IR.Product, IR.Amount FROM dbo.InvoiceRows IR WHERE IR.InvoiceId = I.InvoiceId FOR XML PATH ('InvoiceRow'), TYPE, ROOT ('InvoiceRows') ) FROM dbo.Invoices I FOR XML PATH ('Invoice'), ROOT ('Invoices')
Note the nesting of SQL statements used to create the XML hierarchy.
The results in SSMS looks like this: