在 SQL Server 中使用 FOR XML PATH 查询以获取分层数据的 XML 输出 [英] Query to get XML output for hierarchical data using FOR XML PATH in SQL Server

问题描述

我有一个包含 NodeId、NodeName、ParentNodeId 列的表，我想使用 SQL 查询以 Xml 的形式输出整个表数据，如下所示.我认为 Sql server 中的 FOR XML PATH 模式可用于使用递归来实现这一点(我使用 SQL Server 2008)，但不确定如何.提前致谢

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

<?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，因此显示了菜单的代码.

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

这篇关于在 SQL Server 中使用 FOR XML PATH 查询以获取分层数据的 XML 输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！