More nested XML with SQL Server: n-level tree recursion

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 XMLASBEGIN        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 categoryWHERE        category.CategoryID = 2FOR        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.