TSQL：如何在 XML 中进行自连接以获得嵌套文档？

发布于 2024-07-06 04:31:06 字数 654 浏览 5 评论 0原文

我有一个像这样的 SQL Server 2005 表：

create table Taxonomy(
CategoryId integer primary key,
ParentCategoryId integer references Taxonomy(CategoryId),
CategoryDescription varchar(50) 
)

数据如下 <代码> CategoryIdParentCategoryIdCategoryDescription 123nullfoo345123bar

我想将其查询到一个 xml 文档中，如下所示：

<taxonomy>
<category categoryid="123" categorydescription="foo">
      <category id="455" categorydescription="bar"/>
</category>
</taxonomy>

Is it possible to do this with FOR XML AUTO, ELEMENTS? 或者我需要使用 FOR XML EXPLICIT 吗？

原文

I have a SQL Server 2005 table like this:

create table Taxonomy(
CategoryId integer primary key,
ParentCategoryId integer references Taxonomy(CategoryId),
CategoryDescription varchar(50) 
)

with data looking like
CategoryIdParentCategoryIdCategoryDescription 123nullfoo345123bar

I'd like to query it into an xml document like this:

<taxonomy>
<category categoryid="123" categorydescription="foo">
      <category id="455" categorydescription="bar"/>
</category>
</taxonomy>

Is it possible to do this with FOR XML AUTO, ELEMENTS? Or do I need to use FOR XML EXPLICIT?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

御弟哥哥 2024-07-13 04:31:06

这是可能的，但主要限制是层次结构的级别必须进行硬编码。 SQL Server 联机丛书在此链接。下面是一个生成您请求的 XML 的示例查询：

SELECT [CategoryId] as "@CategoryID"
      ,[CategoryDescription] as "@CategoryDescription"
      ,(SELECT [CategoryId]
       ,[CategoryDescription]
       FROM [dbo].[Taxonomy] "Category"
       WHERE ParentCategoryId = rootQuery.CategoryId
       FOR XML AUTO, TYPE)
FROM [dbo].[Taxonomy] as rootQuery
where [ParentCategoryId] is null
FOR XML PATH('Category'), ROOT('Taxonomy')

It is possible but the main limitation is that the levels of the hierarchy must be hard coded. The SQL Server Books Online has a description of how to represent hierarchies in XML at this link. Below is a sample query that produces the XML you requested:

SELECT [CategoryId] as "@CategoryID"
      ,[CategoryDescription] as "@CategoryDescription"
      ,(SELECT [CategoryId]
       ,[CategoryDescription]
       FROM [dbo].[Taxonomy] "Category"
       WHERE ParentCategoryId = rootQuery.CategoryId
       FOR XML AUTO, TYPE)
FROM [dbo].[Taxonomy] as rootQuery
where [ParentCategoryId] is null
FOR XML PATH('Category'), ROOT('Taxonomy')

回复收藏 0 原文

~没有更多了~