查询在SQL Server中使用FOR XML PATH获取分层数据的XML输出

I have a table with columns NodeId, NodeName, ParentNodeId and I want to ouput entire table data in the form of Xml like the following using SQL query. I think FOR XML PATH mode in Sql server can be used to achieve this (I use SQL Server 2008) using recursion, but not sure how. Thanks in advance

我有一个包含NodeId，NodeName，ParentNodeId列的表，我想使用SQL查询以Xml的形式输出整个表数据。我认为Sql server中的FOR XML PATH模式可以用来实现这个（我使用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>

2 个解决方案

#1

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.

我使用存储过程和递归函数解决了它。代码如下所示。（实际上我想要生成一个菜单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

#2

This query will do it - however, it's not very clean in that you have to "manually" define the nesting and it won't just automatically scale to more levels deep....

这个查询会做到这一点 - 但是，它不是很干净，你必须“手动”定义嵌套，它不会自动扩展到更深层次....

SELECT 
    n.ID AS '@Id', 
    n.NAME AS '@Name',
    (SELECT 
        n2.ID AS '@Id', 
        n2.NAME AS '@Name',
        (SELECT 
            n3.ID AS '@Id', 
            n3.NAME AS '@Name'
         FROM 
            dbo.Nodes n3
         WHERE 
             n3.ParentNode = n2.ID
         FOR XML PATH('Node'), TYPE
        ) 
     FROM 
        dbo.Nodes n2
     WHERE 
         n2.ParentNode = n.ID
     FOR XML PATH('Node'), TYPE
    ) 
FROM 
    dbo.Nodes n
WHERE 
    n.ParentNode IS NULL
FOR XML PATH('Node'), ROOT('Nodes')

Output is:

输出是：

<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>

I was hoping there would be a way to do this with a recursive CTE (Common Table Expression), but that didn't work out :-(

我希望有一种方法可以通过递归CTE（公用表表达式）来做到这一点，但这没有用:-(

#1