Запрос для получения вывода XML для иерархических данных с использованием FOR XML PATH в SQL Server

У меня есть таблица со столбцами NodeId, NodeName, ParentNodeId, и я хочу вывести все данные таблицы в виде Xml, как показано ниже, используя SQL-запрос. Я думаю, что для этого можно использовать режим FOR XML PATH на сервере Sql (я использую 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 sql sql-server tsql for-xml
person RKP    schedule 23.11.2010    source источник
comment
Вы смогли добавить заголовок?   -  person mehtat_90    schedule 16.08.2017

Ответы (2)


arrow_upward
7
arrow_downward

Я решил это, используя хранимую процедуру и рекурсивную функцию. код показан ниже. (на самом деле я хотел, чтобы это сгенерировало 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
person RKP    schedule 23.11.2010

arrow_upward
3
arrow_downward

Этот запрос сделает это, однако он не очень чистый, поскольку вам нужно «вручную» определить вложенность, и он не будет просто автоматически масштабироваться до более глубоких уровней....

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

Выход:

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

Я надеялся, что будет способ сделать это с помощью рекурсивного CTE (Common Table Expression), но это не сработало :-(

person marc_s    schedule 23.11.2010