問題描述

我有一個包含 NodeId、NodeName、ParentNodeId 列的表，我想使用 SQL 查詢以 Xml 的形式輸出整個表數(shù)據(jù)，如下所示.我認為 Sql server 中的 FOR XML PATH 模式可用于使用遞歸來實現(xiàn)這一點(我使用 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>

推薦答案

我使用存儲過程和遞歸函數(shù)解決了這個問題.代碼如下所示.(實際上我希望它生成一個菜單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 查詢以獲取分層數(shù)據(jù)的 XML 輸出的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！