CTE和XML生成嵌套的XML

I have an adjacency list in the DB and want to deliver the data in XML format to the client through a SQL SP. I'm trying to use CTE and FOR XML but I am not getting the XML nodes to nest.

我在DB中有一个邻接列表，希望通过SQL SP将XML格式的数据传递给客户机。

FYI, this will represent a site map.

供大家参考，这将代表一个站点地图。

The Table structure:

表结构:

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)

and the beginnings of the CTE:

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

yields XML that looks like this:

生成如下所示的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>

when what I want is XML that looks like this:

我想要的是这样的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>

I'm guessing the issue is not with the CTE but with the select, but I don't know where to start to fix it. Also, I don't know how deep the nesting will go, so I'm assuming I'll need it to support at least 10 levels deep.

我猜问题不在于CTE，而在于select，但我不知道从哪里开始修复它。而且，我不知道嵌套会有多深，所以我假设我需要它至少支持10层深度。

Edit 1:
I think I'm getting closer... in looking at this page, I created a UDF but still there are some issues:

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

and the SQL that calls the UDF

以及调用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

this will nest the XML for me but it's duplicating nodes which is not what I want..

这将为我嵌套XML，但它是复制节点，这不是我想要的。

Edit 2:

编辑2:

I just needed to add a WHERE clause to the SELECT that calls the UDF:

我只需要在SELECT中添加一个WHERE子句来调用UDF:

...
WHERE ParentPageId IS NULL

3 个解决方案

#1

Turns out I didn't want the CTE at all, just a UDF that I call recursively

原来我根本不想要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

with the SQL that calls the UDF as

使用调用UDF as的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

#2

The question as well as the OP's answer helped me a lot. It took me a bit to grasp the answer as I was missing some context. So here's a seperate answer with a more generic explanation (I've tried to remove every bit of code that didn't relate directly to getting hierarchical data in XML output).

这个问题和OP的回答帮了我很大的忙，我花了一些时间去理解答案，因为我缺少了一些上下文。这里有一个更通用的解释(我尝试删除与在XML输出中获取分层数据没有直接关系的所有代码)。

---

Suppose the following typical table for hierarchical data:

假设以下典型的分层数据表:

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

Suppose it has the following data:

假设它有以下数据:

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

To get hierarchical XML data we could use the following function:

为了得到分层的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;

Which can be called like this:

可以这样称呼:

SELECT dbo.fn_EmployeeHierarchyNode(1)

Or, if you want the root node as well, like this:

或者，如果你也想要根节点，像这样:

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

Which would produce:

这将产生:

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

#3

Recursive CTEs are not recursive as in "nested", they work differently and what you're trying to do doesn't work with CTEs. (Think of them as being always tail-recursive.)

递归cte不像在“嵌套”中那样是递归的，它们的工作方式不同，而您尝试做的事情与cte不一样。(认为它们总是尾部递归的。)

The only way I have found to build recursive XML in SQL Server is by creating a scalar function which renders the nodes recursively; functions can make recursive calls so that this works as expected.

我发现在SQL Server中构建递归XML的唯一方法是创建一个递归呈现节点的标量函数;函数可以进行递归调用，这样就可以正常工作了。