Blog

You are currently viewing Create XML with T-SQL function FOR XML
Microsoft SQL Server

Create XML with T-SQL function FOR XML

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

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has 4 Comments

  1. Quentin 2016/12/16 Reply

    Dear Tomas,

    I’ve found this explanation very useful and tried to replicate it.
    Unfortunately I have not been able to since I get the following error message:

    Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword ‘select’.
    Msg 102, Level 15, State 1, Line 14
    Incorrect syntax near ‘)’.

    Would you mind having a look at the following query? Both select are working separately but I don’t manage to get them together to create the XML hierarchy.

    Kind regards,
    Quentin.

    select
    tst2.cins
    ,tst2.fiche
    ,tst2.policy
    ,tst2.groep
    (
    select
    tst.id
    ,tst.column1
    ,tst.value
    from #TestTable tst
    where tst.pk = tst2.pk
    FOR XML PATH (‘field’), type, root(‘fields’)
    )
    FROM #TestTable2 tst2
    FOR XML PATH (‘record’), root(‘records’)

    1. Tomas Lind 2016/12/16 Reply

      Hi Quentin, there is a missing comma on row 6. Should be: ,(

      Try that first. I also got an error on the string delimiter, but I think that got changed when copying so it might not be a problem for you.

      Regards
      Tomas

  2. Quentin 2016/12/19 Reply

    Hi Thomas,

    I knew it was a small error! Thanks For your help.
    Best end of year wishes to you and your family,

    Quentin.

    1. Tomas Lind 2016/12/19 Reply

      No problem, happy new year wishes to you too 🙂

Leave a Reply