query equivalent: looking for nested XML document as output on a recursive query from sql table

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:

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.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

I am really just starting to learn XML and so I am not sure that I understand your question correctly. Here is some code that seems to produce the output you have requested. Would you please give this a look and tell me if this is headed in the right direction?

Code Snippet

declare @.employee table
( EmployeeId integer,
ManagerId integer,
DepartmentId integer,
Title varchar(18)
)
insert into @.employee
select 109, null, 1, 'Top Dog' union all
select 12, 109, 1, 'Second Dog' union all
select 3, 12, 1, '2-Dog''s Dog' union all
select 4, 3, 1, 'Just Another Dog' union all
select 9, 3, 1, 'Just Another Dog' union all
select 11, 3, 1, 'Just Another Dog' union all
select 158, 3, 1, 'Just Another Dog' union all
select 79, 158, 1, 'Just Another Dog' union all
select 114, 158,1, 'Just Another Dog' union all
select 217, 158,1, 'Just Another Dog' union all
select 263, 3, 1, 'Just Another Dog' union all
select 5, 263, 1, 'Just Another Dog' union all
select 265,263, 1, 'Just Another Dog' union all
select 267, 3, 1, 'Just Another Dog' union all
select 270, 3, 1, 'Just Another Dog'
--select * from @.employee

;with hierarchyCTE as
( select 1 as level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
cast(right(' ' + convert(varchar(11), employeeId), 11) as varchar(240))
as path
from @.employee
where ManagerId is null
union all
select a.level + 1,
b.EmployeeId,
a.EmployeeId,
b.DepartmentId,
b.Title,
cast( a.Path + '/' +
right(' ' + convert(varchar(11), b.employeeId), 11)
as varchar(240))
from hierarchyCTE a
join @.employee b
on a.EmployeeId = b.ManagerId
), sequenceCTE as
( select row_number() over (order by path) as Seq,
level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from hierarchyCTE
), maxSeqCTE as
( select max(seq) as maxSeq
from sequenceCTE
), nextLevelCTE as
( select -1 + row_number() over (order by path) as Seq,
Level
from hierarchyCTE
union all
select seq,
1
from sequenceCTE
join maxSeqCTE
on seq = maxSeq
), outputCTE as
( select cast(a.Seq as integer) as Seq1,
1 as Seq2,
a.level,
b.level as nextLevel,
a.EmployeeId,
a.ManagerId,
a.DepartmentId,
a.Title,
a.Path
from sequenceCTE a
join nextLevelCTE b
on a.Seq = b.Seq
union all
select Seq1,
Seq2 + 1,
level - 1,
nextLevel,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from outputCte
where level > nextLevel
)
select replicate (' ', Level - 1) +
case when Seq2 = 1
then '<Emp ' + 'ManagerID="' +
case when ManagerId is null then ''
else convert(varchar(11), ManagerId)
end + '" EmployeeId="' +
convert(varchar(11), EmployeeId) +
'" DepartmentId="' +
convert(varchar(11), Departmentid) +
'" Title="' +
case when Title is null then ''
else Title
end + '"' +
case when level >= nextLevel
then '/' else ''
end + '>'
else '</Emp>'
end as xmlOutput
from outputCTE
order by Seq1, Seq2

/*
xmlOutput
--
<Emp ManagerID="" EmployeeId="109" DepartmentId="1" Title="Top Dog">
<Emp ManagerID="109" EmployeeId="12" DepartmentId="1" Title="Second Dog">
<Emp ManagerID="12" EmployeeId="3" DepartmentId="1" Title="2-Dog's Dog">
<Emp ManagerID="3" EmployeeId="4" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="9" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="11" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="158" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="158" EmployeeId="79" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="114" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="217" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="263" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="263" EmployeeId="5" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="263" EmployeeId="265" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="267" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="270" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
</Emp>
</Emp>
*/

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

Saturday, February 25, 2012

looking for nested XML document as output on a recursive query from sql table

No comments:

Post a Comment

query equivalent

Blog Archive

About Me