January 10, 2003 at 2:35 am
I have a table (tblHier) with the following 3 columns:-
1) Id int - key column - seeded 1
2) ParentId int - Points to Id on same table
3) Description varchar(30)
I can create a Hierachical tree structure using the above table.
The top root node has the following 3 values:-
1) Id = 1
2) ParentId = 0
3) Description = 'Root'
I need some T-SQL to return a Hierachical XML structure eg.
<Id1>Root
<Id2>Child1</Id2>
<Id3>Child2
<Id5>Child2.1<Id5>
<Id6>Child2.2<Id6>
</Id3>
<Id4>Child3</Id4>
</Id1>
I think I need to use Select 1 as Tag....For XML Explicit?
Any help would be appreciated
Thanks,
Paul.
January 13, 2003 at 8:00 am
This was removed by the editor as SPAM
January 13, 2003 at 6:10 pm
I think you are correct about using FOR XML EXPLICIT. Be warned that it is a big pain to maintain though! BOL actually has a pretty good example on how to do it. Below you will see a self join I created for the Employees table in Northwind. It seems to work and you should be able to modify it to suit your needs...
select DISTINCT 1 as Tag
, NULL as Parent
, M.EmployeeID as [Manager!1!EmployeeID!HIDE]
, M.LastName as [Manager!1!LastName]
, M.FirstName as [Manager!1!FirstName]
, NULL as [UnderLings!2!EmployeeID!HIDE]
, NULL as [UnderLings!2!LastName]
, NULL as [UnderLings!2!FirstName]
FROM Employees M
JOIN Employees E ON M.EmployeeID = E.ReportsTo
UNION ALL
SELECT 2 as Tag
, 1 as Parent
, M.EmployeeID as [Manager!1!EmployeeID!HIDE]
, NULL as [Manager!1!LastName]
, NULL as [Manager!1!FirstName]
, E.EmployeeID as [UnderLings!2!EmployeeID!HIDE]
, E.LastName as [UnderLings!2!LastName]
, E.FirstName as [UnderLings!2!FirstName]
FROM Employees M
JOIN Employees E ON M.EmployeeID = E.ReportsTo
ORDER BY [Manager!1!EmployeeID!HIDE], [UnderLings!2!EmployeeID!HIDE]
FOR XML EXPLICIT
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 13, 2003 at 9:14 pm
Pain to maintain a bit of understatement!
Andy
January 14, 2003 at 2:59 am
Thanks for your help, but the example you gave only allows a Manager to have one level of Underlings.
My table structure allows each managers Underling to have Underlings reporting to him/her (a typical Hierachical tree structure).
Any idea how to do this?
Once again, thanks for your help.
Regards,
Paul.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply