Posts
49
Comments
50
Trackbacks
26
CTE and Hierarchical XML Result

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();

        XPathDocument xpd = new XPathDocument(xr);

        MemoryStream ms = new MemoryStream();

        XmlDocument xslt = new XmlDocument();
        xslt.LoadXml(xsltString);

        XslTransform transform = new XslTransform();
        transform.Load(xslt);
        transform.Transform(xpd, null, ms);

        xmlDest = new SqlXml(ms);

        xr.Close();
        xr.Dispose();
    }
};

Use them!

CREATE ASSEMBLY SP FROM 'c:\public\SqlServerProject3.dll'
GO

CREATE PROCEDURE SP3
@filter INT,
@source XML,
@dest XML OUT
AS
EXTERNAL NAME SP.[StoredProcedures].StoredProcedure1
GO

DECLARE @x1 XML
DECLARE @x2 XML

SET @x1 = (SELECT * FROM vwTest FOR XML AUTO, ROOT('ROOT'))

EXEC SP3 2, @x1, @x2 OUT

SELECT @x2

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!

posted on Sunday, February 06, 2005 2:50 PM Print
News