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:
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:
This is what the output looks like. It'll go for as many levels of depth as your tree does.
Note I had to rearrange some of the categories in the AdventureWorks database to get deeper nesting.