Create XML with T-SQL function FOR XML

Microsoft SQL Server

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:

Invoices Database

Invoices Database

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:

FOR XML Results

FOR XML Results

4 comments » Write a comment

Leave a Reply

Required fields are marked *.