我使用存储过程和递归函数解决了它。如下所示的代码。(实际上,我希望它生成一个菜单xml,因此将显示该菜单的代码。
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