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

I have a SQL Server 2005 table like this:

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

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

with data looking like CategoryIdParentCategoryIdCategoryDescription 123nullfoo345123bar


数据看起来像CategoryIdParentCategoryIdCategoryDe​​scription 123nullfoo345123bar

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

我想将它查询到像这样的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? Or do I need to use FOR XML EXPLICIT?

是否可以使用FOR XML AUTO,ELEMENTS执行此操作?或者我需要使用FOR XML EXPLICIT吗?

1 个解决方案

#1

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:

这是可能的,但主要限制是层次结构的级别必须是硬编码的。 SQL Server联机丛书描述了如何在此链接中表示XML中的层次结构。下面是一个生成您请求的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')