我有一个表有NodeId,NodeName,ParentNodeId的列,我想以Xml的形式输出整个表数据,如下所示使用SQL查询.我认为FOR sql PATH模式在sql server中可以用来实现这个(我使用sql Server 2008)使用递归,但不知道如何.提前致谢
<?xml version="1.0" encoding="utf-8" ?>
<Nodes>
<Node Id="1" Name="node1">
<Node Id="11" Name="node11">
<Node Id="111" Name="node111" />
<Node Id="112" Name="node112" />
</Node>
</Node>
<Node Id="2" Name="node2">
<Node Id="21" Name="node21">
<Node Id="211" Name="node211" />
<Node Id="212" Name="node212" />
</Node>
</Node>
</Nodes>
解决方法
我使用存储过程和递归函数来解决它.代码如下. (实际上我想要生成一个菜单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
