CTE和FOR XML生成XML嵌套(CTE and FOR XML to generate nes

我在DB的邻接表，并希望通过SQL SP以XML格式的数据传送到客户端。我试图使用CTE和FOR XML，但我没有得到的XML节点窝。

仅供参考，这将是一个网站地图。

表结构：

CREATE TABLE [dbo].[PageHierarchy](
    [ModuleId] [int] NOT NULL,
    [PageId] [int] IDENTITY(1,1) NOT NULL,
    [ParentPageId] [int] NULL,
    [PageUrl] [nvarchar](100) NULL,
    [PageTitle] [nvarchar](50) NOT NULL,
    [PageOrder] [int] NULL)

和CTE的开端：

;WITH cte AS
(
    select * from PageHierarchy where ParentPageId is null
    union all
    select child.* from PageHierarchy child inner join cte parent on parent.PageId = child.ParentPageId
)
SELECT ModuleId, PageId, ParentPageId, PageUrl, PageTitle, PageOrder FROM cte
group by ModuleId, PageId, ParentPageId, PageUrl, PageTitle, PageOrder
order by PageOrder
for xml auto, root ('bob')

产生XML，看起来像这样：

<bob>
  <cte ModuleId="1" PageId="1" PageUrl="~/Admin/" PageTitle="Administration" PageOrder="1000" />
  <cte ModuleId="1" PageId="4" ParentPageId="1" PageTitle="Manage Users" PageOrder="1030" />
  <cte ModuleId="1" PageId="5" ParentPageId="4" PageUrl="~/Admin/AddUser" PageTitle="Add Users" PageOrder="1040" />
  <cte ModuleId="1" PageId="8" ParentPageId="4" PageUrl="~/Admin/EditUser" PageTitle="Edit/Search User" PageOrder="1070" />
</bob>

当我要的是XML，看起来像这样：

<bob>
  <cte ModuleId="1" PageId="1" PageUrl="~/Admin/" PageTitle="Administration" PageOrder="1000" />
  <cte ModuleId="1" PageId="4" ParentPageId="1" PageTitle="Manage Users" PageOrder="1030" >
    <cte ModuleId="1" PageId="5" ParentPageId="4" PageUrl="~/Admin/AddUser" PageTitle="Add Users" PageOrder="1040" />
    <cte ModuleId="1" PageId="8" ParentPageId="4" PageUrl="~/Admin/EditUser" PageTitle="Edit/Search User" PageOrder="1070" />
  </cte>
</bob>

我猜这个问题是不是与CTE但与选择，但我不知道从哪里开始解决它。另外，我不知道该嵌套将有多深，所以我假设我需要它至少支持10级深。

编辑1：
我觉得我越来越近......在看这个页面，我创建了一个UDF，但仍存在一些问题：

CREATE FUNCTION PageHierarchyNode(@PageId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
  (SELECT ModuleId AS "@ModuleId", PageId AS "@PageId",
    ParentPageId AS "@ParentPageId", PageUrl AS "@PageUrl",
    PageTitle AS "@PageTitle", PageOrder AS "@PageOrder", 
      CASE WHEN ParentPageId=@PageId
      THEN dbo.PageHierarchyNode(PageId)
      END
   FROM dbo.PageHierarchy WHERE ParentPageId=@PageId
   FOR XML PATH('Page'), TYPE)
END

并调用UDF的SQL

SELECT ModuleId AS "@ModuleId", PageId AS "@PageId",
    ParentPageId AS "@ParentPageId", PageUrl AS "@PageUrl",
    PageTitle AS "@PageTitle", PageOrder AS "@PageOrder", 
    dbo.PageHierarchyNode(PageId)
FROM PageHierarchy
FOR XML PATH('Page'), ROOT('SiteMap'), TYPE

这将巢XML但对我来说它的复制节点这是不是我想要的..

编辑2：

我只是需要一个WHERE子句添加到调用UDF的SELECT：

...
WHERE ParentPageId IS NULL

Answer 1:

原来我不想CTE，在所有的，只是一个UDF我称之为递归

CREATE FUNCTION PageHierarchyNode(@PageId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
  (SELECT ModuleId AS "@ModuleId", PageId AS "@PageId",
    ParentPageId AS "@ParentPageId", PageUrl AS "@PageUrl",
    PageTitle AS "@PageTitle", PageOrder AS "@PageOrder", 
      CASE WHEN ParentPageId=@PageId
      THEN dbo.PageHierarchyNode(PageId)
      END
   FROM dbo.PageHierarchy WHERE ParentPageId=@PageId
   FOR XML PATH('Page'), TYPE)
END

与调用UDF的SQL

SELECT ModuleId AS "@ModuleId", PageId AS "@PageId",
    ParentPageId AS "@ParentPageId", PageUrl AS "@PageUrl",
    PageTitle AS "@PageTitle", PageOrder AS "@PageOrder", 
    dbo.PageHierarchyNode(PageId)
FROM PageHierarchy
WHERE ParentPageId IS NULL
FOR XML PATH('Page'), ROOT('SiteMap'), TYPE

Answer 2:

这个问题还有OP的回答对我帮助很大。我花了几分把握的答案，因为我错过了一些背景。因此，这里是一个更通用的解释一个单独的答案（我试过删除代码的每一位未直接涉及到获取XML输出中的分层数据）。

假设分层数据以下典型表：

CREATE TABLE Employee (Id INT, BossId INT, Name NVARCHAR(50));

假设它有以下数据：

INSERT INTO Employee (Id, BossId, Name) VALUES
(1, NULL, 'Boss Pancone'),
(2, 1, 'Capioregime Luciano'),
(3, 1, 'Capioregime Bruno'),
(4, 2, 'Johnny'),
(5, 2, 'Luca'),
(6, 2, 'Luciano jr.'),
(7, 3, 'Marco'),
(8, 3, 'Mario'),
(9, 3, 'Giacomo');

为了让我们可以使用下面的功能分层的XML数据：

ALTER FUNCTION dbo.fn_EmployeeHierarchyNode (@BossId INT) RETURNS XML
BEGIN RETURN
    (SELECT Id, 
            BossId, 
            Name,
            dbo.fn_EmployeeHierarchyNode(Id)
        FROM Employee
        WHERE BossId = @BossId
        FOR XML AUTO)
END;

这可以称得上是这样的：

SELECT dbo.fn_EmployeeHierarchyNode(1)

或者，如果你想根节点为好，就像这样：

SELECT  Id,
        BossId,
        Name,
        dbo.fn_EmployeeHierarchyNode(Id)
FROM    Employee
WHERE   BossId IS NULL
FOR     XML AUTO

这将产生：

<Employee Id="1" Name="Boss Pancone">
  <Employee Id="2" BossId="1" Name="Capioregime Luciano">
    <Employee Id="4" BossId="2" Name="Johnny" />
    <Employee Id="5" BossId="2" Name="Luca" />
    <Employee Id="6" BossId="2" Name="Luciano jr." />
  </Employee>
  <Employee Id="3" BossId="1" Name="Capioregime Bruno">
    <Employee Id="7" BossId="3" Name="Marco" />
    <Employee Id="8" BossId="3" Name="Mario" />
    <Employee Id="9" BossId="3" Name="Giacomo" />
  </Employee>
</Employee>