nested set model

Hi,

I am storing some hierarchical information using the nested set model. I would like to transform the data into XML to bind them to a TreeView object. Did someone do something similar before. Any input would be very much appreciated. Many thanks.

Christian

SQL Server 2000 or 2005?

Also you'll need to post some DDL to get any sort of answer

|||

-- An example for SQL Server 2005 only, may be helpful

CREATE TABLE MyTable(Name VARCHAR(10),lft INT,rgt INT)

INSERT INTO MyTable(Name,lft,rgt)
SELECT 'Top' , 1 , 12 UNION ALL
SELECT 'Level1A' ,2 , 3 UNION ALL
SELECT 'Level1B' ,4 , 11 UNION ALL
SELECT 'Level2A' ,5 , 6 UNION ALL
SELECT 'Level2B' ,7 , 8 UNION ALL
SELECT 'Level2C' ,9 , 10

GO
CREATE VIEW Adjacency
-- Converts the nested set to an adjacency list
AS
SELECT P.Name AS ParentName,
N.Name
FROM MyTable AS N
LEFT OUTER JOIN MyTable AS P ON P.lft = (SELECT MAX(S.lft)
FROM MyTable AS S
WHERE N.lft > S.lft
AND N.lft < S.rgt)
GO
CREATE FUNCTION dbo.SubTree(@.Name VARCHAR(10))
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName=@.Name
ORDER BY Name
FOR XML PATH('Node'),TYPE)
END

GO

SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName IS NULL
ORDER BY Name
FOR XML PATH('Node'),ROOT('Nodes'),TYPE

|||

Thanks Mark. This helped. I got it all working fine.

Chris