Posts
49
Comments
50
Trackbacks
26
August 2004 Blog Posts
CTE+XML Limitation?

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 :-)

posted @ Tuesday, August 31, 2004 11:03 AM | Feedback (2)
Here I Am

Hi to all readers!

I've been playing with SQL Server 2000 a lot, and now it's time to start to play seriusly with SQL Server 2005. I'm a SQL, XML and CLR lover, so SQL Server 2005 is my perfect enviroment.

Here i'd like to post all experiences that i'll make with it, to share knowledge with you and to learn from you as well.

Stay tuned!

posted @ Tuesday, August 31, 2004 7:33 AM | Feedback (0)
News