Davide Mauri Blog

Is it possible to finally have a nested XML document on a recursive query? Let's say that we have a classic parent-child relationship inside a table that is self-refernced. An example of this situation can be found in the Employees table of Northwind DB or in the shining new AdventureWorks DB, HumanResources.Employee table.
In both cases we have somthing like that:

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)
AS
(
    SELECT
  ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
    FROM
  HumanResources.Employee
    WHERE
  ManagerID IS NULL
   
 UNION ALL
   
 SELECT
  e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))
    FROM
  HumanResources.Employee AS e
    JOIN
  DirectReports AS d
    ON
  e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports
ORDER BY Path

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

<Emp ManagerID="109" EmployeeID="12" ... >
  <Emp ManagerID="12" EmployeeID="3" ... >
  <Emp ManagerID="3" EmployeeID="4" ... />
  <Emp ManagerID="3" EmployeeID="9" ... />
  <Emp ManagerID="3" EmployeeID="11" ... />
  <Emp ManagerID="3" EmployeeID="158" ... >
    <Emp ManagerID="158" EmployeeID="79" ... />
    <Emp ManagerID="158" EmployeeID="114" ... />
    <Emp ManagerID="158" EmployeeID="217" ... />
  </Emp>
  <Emp ManagerID="3" EmployeeID="263" ... >
    <Emp ManagerID="263" EmployeeID="5" ... />
    <Emp ManagerID="263" EmployeeID="265" ... />
  </Emp>
    <Emp ManagerID="3" EmployeeID="267" ... />
    <Emp ManagerID="3" EmployeeID="270" ... />
  </Emp>
</Emp>

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them. 

Feedback appreciated :-)