TSQL: How to do self-merging in XML to get an embedded document?

Question

I have a SQL Server 2005 table:

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

with data similar to 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>

Can this be done using FOR XML AUTO, ELEMENTS? Or do I need to use FOR XML EXPLICIT?

Answer 1

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