更新时间:2023-02-03 12:55:19
我使用存储过程和递归函数解决了这个问题.代码如下所示.(实际上我希望它生成一个菜单xml,因此显示了菜单的代码.
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.
CREATE PROCEDURE [dbo].[usp_GetMenu]
AS
BEGIN
SET NOCOUNT ON;
SELECT dbo.fnGetMenuItems(MenuId)
FROM dbo.Menu
WHERE ParentMenuId IS NULL
FOR XML PATH('MenuItems')
END
GO
CREATE FUNCTION [dbo].[fnGetMenuItems]
(
@MenuId int
)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN
(
SELECT MenuId AS "@Id"
, [Name] AS "@Name"
, [URL] AS "@URL"
, [Key] AS "@Key"
, [dbo].[fnGetMenuItems](MenuId)
FROM dbo.Menu
WHERE ParentMenuId = @MenuId
FOR XML PATH('MenuItem'),TYPE
)
END
GO