Nested FOR XML results with SQL Server’s PATH mode

Today, while doing some work on a highly data (not object) driven .NET application, I needed a query output as XML from the application’s SQL Server 2005 database. I wanted:

  • Nicely formatted and properly mapped XML (e.g. no <row> elements as found in FOR XML RAW mode)
  • To be able to easily map columns to XML elements and attributes
  • A single root node, so I can load it into an XmlDocument without having to create the root node myself
  • Nested child elements
  • Not to have to turn my elegant little query into a huge mess of esoteric T-SQL (as with [Explicit!1!Mode])

I discovered that all this is surprisingly easy to achieve all of these things with SQL Server 2005′s FOR XML PATH mode. (I say surprising, because I’ve tried this sort of thing with FOR XML AUTO a few times before under SQL Server 2000, and gave up each time).

Here’s a quick example I’ve created using the venerable AdventureWorks example database, with comments against all the important bits:

SELECT
	-- Map columns to XML attributes/elements with XPath selectors.
	category.ProductCategoryID AS '@id',
	category.Name AS '@name',
	(
		-- Use a sub query for child elements.
		SELECT
			ProductID AS '@id',
			Name AS '@name',
			ListPrice AS '@price'
		FROM
			SalesLT.Product
		WHERE
			ProductCategoryID = category.ProductCategoryID
		FOR
			XML PATH('product'), -- The element name for each row.
			TYPE -- Column is typed so it nests as XML, not text.
			
	) AS 'products' -- The root element name for this child collection.
FROM
	SalesLT.ProductCategory category
FOR
	XML PATH('category'), -- The element name for each row.
	ROOT('categories') -- The root element name for this result set.

As you can see, we’ve mapped columns to attributes/elements with XPath selectors, and set row and root element names with PATH() and ROOT() respectively.

Plus, by specifying my own names for everything, I was also able to address the difference in capitalization, prefixing and pluralization style between the AdventureWorks’ database table names and common XML.

Running this query produces output in the following format. Note the root nodes for both outer and child collections:

<categories>
  <category id="4" name="Accessories" />
  <category id="24" name="Gloves">
    <products>
      <product id="858" name="Half-Finger Gloves, S" price="24.4900" />
      <product id="859" name="Half-Finger Gloves, M" price="24.4900" />
      <product id="860" name="Half-Finger Gloves, L" price="24.4900" />
      <product id="861" name="Full-Finger Gloves, S" price="37.9900" />
      <product id="862" name="Full-Finger Gloves, M" price="37.9900" />
      <product id="863" name="Full-Finger Gloves, L" price="37.9900" />
    </products>
  </category>
  <category id="35" name="Helmets">
    <products>
      <product id="707" name="Sport-100 Helmet, Red" price="34.9900" />
      <product id="708" name="Sport-100 Helmet, Black" price="34.9900" />
      <product id="711" name="Sport-100 Helmet, Blue" price="34.9900" />
    </products>
  </category>
</categories>

You can read more about using FOR XML PATH here.

August 26, 2008

15 Comments

John on February 27, 2009 at 6:36 am.

Thanks for the great example. BOL alludes to this, but doesn’t give any real examples, and the TYPE keyword was why I couldn’t get it working correctly (because I never included it in my query). Thanks so much, as this makes querying the db with BizTalk much much easier to do.

Micah on August 7, 2009 at 8:08 am.

This is very close to what I’ve been looking for.
Using your example above, how would you remove the element and simply toss the elements under a element?

Micah on August 7, 2009 at 8:09 am.

This is very close to what I’ve been looking for.
Using your example above, how would you remove the ‘products’ element and simply toss the ‘product’ elements under a ‘category’ element?

Richard on August 7, 2009 at 9:27 am.

@Micah: I’m not sure if it’s possible to merge the elements for the outer and inner queries like that with SQL.

AB on March 30, 2010 at 6:28 pm.

Good on ya mate – exactly what I was looking for

Eric on April 7, 2010 at 12:17 am.

This works for me too, but a problem I have is the final FROM XML when I use the Directive TYPE I do not get and XML output. However if I remove the TYPE directive I do get the output in Mangement Studio. I think I may have a bad character or somethign but I am not finding anything. Also how do you do CDATA with XML PATH. Thanks fo rthe example

Aaron Walker on August 24, 2010 at 6:40 pm.

Micah-

You can remove the “products” element and put the nested “product” elements directly under the “category” element by changing the “AS ‘products’” line to “AS ‘category’”.

By re-using the parent element name here the child elements are nested directly below the parent.

Weaponx on April 18, 2012 at 10:33 am.

Love it – great article.

David Morello on May 31, 2012 at 11:13 pm.

@Richard, thank you so much. I have been looking for this. Other posts have come close without getting this exactly what I was looking for.

@Aaron, nice update. I can now use this two different ways.

Ryan Short on September 5, 2012 at 3:45 pm.

Thanks, just what I was looking for. The tip about the TYPE part was what I kept missing.

Payam Lashkari on October 11, 2013 at 2:18 am.

Thank you so much! I really needed this and I was just searching till I got here :) Thanks a lot!

Leave a Reply