Finally i've managed to have a hierarchical xml result from a CTE Query.

I've discovered that SQL Server 2005 cannot do this natively, so at the end i've create a .NET Stored Procedure that takes the XML as it cames out from the CTE and produces a hierarchical one.

This is the code so that you can test yourself:

Create a sample table with sample values:

create table PartsTree (id int, parent int)
go

insert into PartsTree values (1, null)
insert into PartsTree values (2, null)
insert into PartsTree values (3, 2)
insert into PartsTree values (4, 2)
insert into PartsTree values (5, 3)
go

Create the CTE and make a view on it

create view vwTest as
with descendant(parent, id, level)
as
(
    select parent, id, 1 as level from PartsTree where id = 2

    union all

    select p.parent, p.id, d.level + 1 from PartsTree p 
    inner join descendant d on d.id = p.parent
)
select parent, id from descendant
go

Create the .NET Procedure

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void StoredProcedure1(int filter, SqlXml xmlSource, out SqlXml xmlDest)
    {
        string xsltString =
        @"<?xml version=""1.0""?>
        <xsl:stylesheet xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"" version=""1.0"">
        <xsl:output method=""xml"" encoding=""Windows-1252""/>
            <xsl:template match=""/ROOT"">
                <xsl:apply-templates select=""vwTest[@id=" + filter.ToString() + @"]""></xsl:apply-templates>
            </xsl:template>
            <xsl:template match=""vwTest"">
                <xsl:variable name=""id"" select=""@id""></xsl:variable>
                <vwTest>
                <xsl:for-each select=""@*"">
                    <xsl:attribute name=""{name(.)}""><xsl:value-of select="".""/></xsl:attribute>
                </xsl:for-each>
                <xsl:apply-templates select=""//vwTest[@parent=$id]""/>
                </vwTest>
            </xsl:template>
        </xsl:stylesheet>";

        XmlReader xr = xmlSource.CreateReader();

Of course this solution has several limits. The bigger one is that the XML that come out from the CTE must have a root called "ROOT" and the elements called "vwTest". In addition elements must have two attributes called "id" and "parent".

Anyway i'm going to improve it as soon as i have some free time, but someone may found useful even this version (maybe as a starting point), so i've shared it with you.

As usual any feedback is welcome!