During my foray into XML SQL queries this week, I was presented with another challenge. Instead of getting just the immediate children of a category, I now needed to recursively select all children from a tree – to an unlimited depth.

A Common Table Expression (CTE, aka WITH statement) can also be called recursively, but requires UNION ALL to join the recursive and anchor members — and XML columns can’t be unioned.

Instead, we need a user defined function that returns XML TYPE. It’ll give us a rootless collection of products within a category, and call itself again to get sub-categories. Got it? Here’s the function definition, again using the AdventureWorks database:

CREATE FUNCTION GetProductCategoryChildren
(
	@ParentProductCategoryID INT
)
RETURNS XML
AS
BEGIN
	RETURN
	(
	SELECT
		-- Map columns to XML attributes/elements with XPath selector
		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',
		dbo.GetProductCategoryChildren(category.ProductCategoryID)
			AS 'categories' -- Recursive call to get child categories.
	FROM
		SalesLT.ProductCategory category
	WHERE
		category.ParentProductCategoryID = @ParentProductCategoryID
	FOR
		XML PATH('category'),  -- The element name for each row.
		TYPE -- The root element name for this result set.
	)
END

This function works great. But we still want to get details about the group itself (not just its children), and we still need a root node so we can load it into an XmlDocument. Here’s how to wrap the call to this function to get a root node and details about the parent:

-- Get the parent group's name and child products.
SELECT
	category.ProductCategoryID AS '@id',
	category.Name AS '@name',
	(
		SELECT
			ProductID AS '@id',
			Name AS '@name',
			ListPrice AS '@price'
		FROM
			SalesLT.Product
		WHERE
			ProductCategoryID = category.ProductCategoryID
		FOR
			XML PATH('product'), TYPE
	) AS 'products',
	-- start recursing to get child categories.
	dbo.GetProductCategoryChildren(category.ProductCategoryID) AS 'categories'
FROM
	SalesLT.ProductCategory category
WHERE
	category.CategoryID = 2
FOR
	XML PATH('category'), ROOT('categories')

This is what the output looks like. It’ll go for as many levels of depth as your tree does.

<categories>
  <category id="2" name="Components">
    <categories>
      <category id="8" name="Handlebars">
        <products>
          <product id="808" name="LL Mountain Handlebars" price="44.5400" />
          <product id="809" name="ML Mountain Handlebars" price="61.9200" />
          <product id="810" name="HL Mountain Handlebars" price="120.2700" />
          <product id="811" name="LL Road Handlebars" price="44.5400" />
          <product id="812" name="ML Road Handlebars" price="61.9200" />
        </products>
        <categories>
          <category id="9" name="Bottom Brackets">
            <products>
              <product id="994" name="LL Bottom Bracket" price="53.9900" />
              <product id="995" name="ML Bottom Bracket" price="101.2400" />
              <product id="996" name="HL Bottom Bracket" price="121.4900" />
            </products>
            <categories>
              <category id="11" name="Chains">
                <products>
                  <product id="952" name="Chain" price="20.2400" />
                </products>
              </category>
            </categories>
          </category>
          <category id="10" name="Brakes">
            <products>
              <product id="907" name="Rear Brakes" price="106.5000" />
              <product id="948" name="Front Brakes" price="106.5000" />
            </products>
          </category>
        </categories>
      </category>
      <category id="12" name="Cranksets">
        <products>
          <product id="949" name="LL Crankset" price="175.4900" />
          <product id="950" name="ML Crankset" price="256.4900" />
          <product id="951" name="HL Crankset" price="404.9900" />
        </products>
      </category>
    </categories>
  </category>
</categories>

Note I had to rearrange some of the categories in the AdventureWorks database to get deeper nesting.