Build XML Off of Common Table Expression

Question

Hi All,

I am using a CTE to recurse data I have stored in a recursive table. The trouble is I am trying to figure out how I can use "FOR XML" to build the desired xml output. I have a Table of Contents table I am recursing and I want to be able to use that data to generate the XML.

Here is an example of what the data is simliar to:

ID|TOC_ID|TOC_SECTION|TOC_DESCRIPTON|PARENT_ID
1|I|Chapter|My Test Chapter|-1
2|A|Section|My Test Section|1
3|1|SubSection|My SubSection|2

I want to be able to spit out the data like so:

XML Attributes: ID = Appended values from the TOC_ID field value = value from TOC_Section field

<FilterData>
  <Filter id="I" value="Chapter">
    <Description>My Test Chapter</Description>
      <Filter id="I_A" value="Section">
        <Description>My Test Section</Description>
          <Filter id="I_A_1" value="SubSection">
            <Description>My Test SubSection</Description>
          </Filter>
      </Filter>
  </Filter>
</FilterData>

Not sure how I can take the CTE data and produce a similar format to the above. When the data is in separate tables it isn't too difficult to build this type of output.

As always appreciate the input.

Thanks,

S

Answer 1

You may get some mileage from Recursive Hierarchies to XML in Christian Wade's blog - it all looks mighty painful to me!

Answer 2

Check this out Will (Not sure you are still following)....this does have a 32 level max, but that hsould still work fine for my stuff...can't see going deeper than that. Found this on another forum:

CREATE TABLE tree ( id INT, name VARCHAR(5), parent_id INT )

GO

INSERT INTO tree VALUES ( 1, 'N1', NULL )

INSERT INTO tree VALUES ( 3, 'N4', 1 )

INSERT INTO tree VALUES ( 4, 'N10', 3 )

INSERT INTO tree VALUES ( 5, 'N7', 3 )

GO

CREATE FUNCTION dbo.treeList(@parent_id int)

RETURNS XML

WITH RETURNS NULL ON NULL INPUT

BEGIN RETURN

(SELECT id as "@id", name as "@name",

CASE WHEN parent_id=@parent_id

THEN dbo.treeList(id)

END

FROM dbo.tree WHERE parent_id=@parent_id

FOR XML PATH('tree'), TYPE)

END

GO

SELECT id AS "@id", name AS "@name",

CASE WHEN id=1

THEN dbo.treeList(id)

END

FROM tree

WHERE id=1

FOR XML PATH('tree'), TYPE

Now isn't that nice and simple?

Customised from the great example over on http://msdn.microsoft.com/en-us/library/ms345137.aspx