A function that returns XML from a recursive query?

  • I wrote a function that I am not happy with the way it turned out. It recursively climbs the hierarchy of my parent/child datatable and returns the results as XML.

    The problem is that I couldn't find any way to SET a variable to the results of a SELECT statement that begins with WITH (uses a CTE).

    What I would've liked to do is this: (Doesn't work)

    CREATE FUNCTION spf_IdHierarchy(@Id INT)

    RETURNS XML

    AS

    BEGIN

    declare @ret XML;

    SET @ret = (

    WITH IdHierarchy (Id, IdLevel)

    AS

    (

    SELECT @Id Id, 1 IdLevel

    UNION ALL

    SELECT tob.ParentId, IdLevel+1

    FROM dbo.TreeObjects tob INNER JOIN IdHierarchy ih ON tob.Id = ih.Id AND ParentId > 0

    )

    SELECT Id, ROW_NUMBER() OVER (ORDER BY IdLevel DESC) Level

    FROM IdHierarchy Id

    FOR XML AUTO, ROOT('Ids')

    )

    RETURN @ret

    END

    What I was forced to do was this:

    ALTER FUNCTION dbo.spf_IdHierarchy ( @Id INT )

    RETURNS XML

    AS BEGIN

    DECLARE @ret XML ;

    DECLARE @Ids TABLE ( Id INT, Level INT ) ;

    WITH IdHierarchy ( Id, IdLevel )

    AS ( SELECT @Id Id,

    1 IdLevel

    UNION ALL

    SELECT tob.ParentId,

    IdLevel + 1

    FROM dbo.TreeObjects tob

    INNER JOIN IdHierarchy ih ON tob.Id = ih.Id

    AND ParentId > 0

    )

    INSERT @Ids ( Id, Level )

    SELECT Id,

    ROW_NUMBER() OVER ( ORDER BY IdLevel DESC ) Level

    FROM IdHierarchy Id

    SET @ret = ( SELECT Id,

    Level

    FROM @Ids Id

    FOR

    XML AUTO,

    ROOT('Ids')

    )

    RETURN @ret

    END

    Is there any way I could have done this without a cheesy table variable?

    Warm Regards,Greg Wilsonsolidrockstable.com

  • Would this work?

    [font="Courier New"]CREATE FUNCTION spf_IdHierarchy(@Id INT)

    RETURNS XML

    AS

    BEGIN

       DECLARE @ret XML;

       WITH IdHierarchy (Id, IdLevel)

       AS

       (

       SELECT @Id Id, 1 IdLevel

       UNION ALL

       SELECT tob.ParentId, IdLevel+1

       FROM dbo.TreeObjects tob INNER JOIN IdHierarchy ih ON tob.Id = ih.Id AND ParentId > 0

       )

       SELECT

           @ret = (

                   SELECT

                       Id,

                       ROW_NUMBER() OVER (ORDER BY IdLevel DESC) LEVEL

                   FROM

                       IdHierarchy Id

                   FOR XML AUTO, ROOT('Ids')

                   )

      

       RETURN @ret

    END

    [/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply