CTE和FOR XML生成嵌套XML [英] CTE and FOR XML to generate nested XML

问题描述

我在数据库中有一个邻接表，想通过XML SP将XML格式的数据传递给客户端。我正在尝试使用CTE和FOR 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.

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

FYI, this will represent a site map.

表结构：

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：

yields XML that looks like this:

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

when what I want is XML that looks like this:

<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，而在于select，但我没有知道从哪里开始修复它。另外，我不知道嵌套的深度，因此我假设我需要它来支持至少10个深度。

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.

编辑1：

我想我越来越近了……正在查看此页面，我创建了UDF，但仍然存在一些问题：

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

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

and the SQL that calls the UDF

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，但是它正在复制节点，这不是什么我想要..

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

编辑2：

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

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

...
WHERE ParentPageId IS NULL

推荐答案

结果我根本不希望CTE，而只是想要的UDF我递归地调用

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

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

with the SQL that calls the UDF as

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

这篇关于CTE和FOR XML生成嵌套XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！