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.
