Load nested unordered list menu from sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Load nested unordered list menu from sql server

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2012-06-13 : 07:41:41

DECLARE @Menu TABLE
(
IDMenu INT,
IDParent INT,
Name NVARCHAR(255),
Link NVARCHAR(255)
);

INSERT INTO @Menu
SELECT 1, NULL, 'Home', 'index.aspx' UNION ALL
SELECT 2, NULL, 'File', '#' UNION ALL
SELECT 3, 2, 'Open File', 'open.aspx' UNION ALL
SELECT 4, 2, 'New File', 'new.aspx' UNION ALL
SELECT 5, 2, 'Delete File', '#' UNION ALL
SELECT 6, NULL, 'Edit', 'edit.aspx' UNION ALL
SELECT 7, NULL, 'logout', 'logout.aspx' UNION ALL
SELECT 8, 5, 'Update File', 'Update.aspx' UNION ALL
SELECT 9, 5, 'Truncate File','Truncate.aspx' UNION ALL
SELECT 10, 9, 'Drop File', '#' UNION ALL
SELECT 11, 10, 'Drop File', 'Drop.aspx'

;WITH Hierarchy AS
(
SELECT A.IDMenu,
A.IDParent,
A.Name,
A.Link,
'\\' + CAST(A.IDMenu AS VARCHAR(MAX)) AS MenuPath,
A.IDMenu AS IDRoot
FROM @Menu A
WHERE A.IDParent IS NULL
UNION ALL
SELECT C.IDMenu,
C.IDParent,
C.Name,
C.Link,
P.MenuPath + '\' + CAST(C.IDMenu AS VARCHAR(MAX)),
P.IDMenu
FROM @Menu C
INNER JOIN Hierarchy P ON C.IDParent = P.IDMenu
)

--SELECT * FROM Hierarchy

SELECT O.Link AS [a/@href],
O.Name AS [a],
(
SELECT I.Link AS [a/@href],
I.Name AS [a]
FROM Hierarchy I
WHERE I.IDParent = O.IDRoot
ORDER BY I.MenuPath
FOR XML PATH('li'), TYPE
) AS [ul]
FROM Hierarchy O
WHERE O.IDMenu = O.IDRoot
ORDER BY O.MenuPath
FOR XML PATH ('li'), ROOT ('ul') ;

The table is more nested.
How can I.

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-13 : 08:54:16

As far as I know, XML PATH does not have any native features that allow one to compose XML from hierarchical data that is of arbitrary depth. I can visualize how it can be done using XML EXPLICT, but a) I have not tried it, b) the query can get incredibly complex (at least for my level of skills).

What I have seen people do is wrap the recursion in a UDF and then use the UDF together with XML PATH to compose the XML. You will find examples if you Google for recursive XML path. Here is one, for example: http://stackoverflow.com/questions/4256900/query-to-get-xml-output-for-hierarchical-data-using-for-xml-path-in-sql-server

baburk
Posting Yak Master

108 Posts

Posted - 2012-06-15 : 03:13:02

The hierarchy are created dynamically. So the depth will increase to more levels.

This won't help
http://stackoverflow.com/questions/4256900/query-to-get-xml-output-for-hierarchical-data-using-for-xml-path-in-sql-server

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-15 : 08:04:59

quote:
Originally posted by baburk

The hierarchy are created dynamically. So the depth will increase to more levels.

This won't help
http://stackoverflow.com/questions/4256900/query-to-get-xml-output-for-hierarchical-data-using-for-xml-path-in-sql-server

Not sure which answer you were looking at. I was referring to the one that is marked with the green check mark and starts with "I solved it using a stored procedure and a recursive function. code shown below. (actually I wanted this to generate a menu xml, so the code is shown for the menu." The code s/he posted calls the function recursively, so it can handle dynamic hierarchies of unknown depth.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources