Appending XMLELEMENTs to existing XMLType

From: <dataplex_at_gmail.com>
Date: 11 Aug 2006 10:03:02 -0700
Message-ID: <1155315782.449511.212600@74g2000cwt.googlegroups.com>

I have a self referencing table that I'm trying to build an XML hierarchy with. The table Categories layout looks like this: CategoryID Number NOT NULL Primary,
ParentID Number,
Name varchar2(200),
isActive Number(0,1)

I am using this query so far to pull out the XML elements:

SELECT xmlelement( "Categories",

                    xmlagg( xmlelement( "Category",
                              xmlattributes( cata.categoryid as "id",
cata.name as "name"),
                              xmlelement( "Category",
                                    xmlattributes( catb.categoryid as
"id", catb.name as "name"))
                            )
                    )
                  ).getclobval()

FROM Maps.Categories cata, Maps.Categories catb WHERE catb.ParentID = cata.CategoryID

However, what I want is for the self-referencing hierarchy to be displayed as an xmlagg of nested nodes. Is there a way I can do that with a simple query, or will I have to write a recursive function to dig down each level? If I must take this approach, does anyone know how to append the XML (in varchar2 or XMLType) nodes to the parent node?

For example (psuedocode):
function processCategory(catID IN Number) returns XMLNode {

     (string or xmltype) catXML = getCategoryXML();
     for each childNode in (SELECT * FROM Categories WHERE parentID =
catID)
                 catXML = concatenate processCategory(childID)
     end for loop
     return catXML

}

Right now I get xml that looks like this: <Categories><Category id="1" name="Categories"><Category id="2" name="ParentCat1" /></Category><Category id="1" name="Categories"><Category id="3" name="ParentCat2" /></Category></Categories>

What I want is:
<Categories>

Any help would be appreciated as I'm on a tight timeline and if I can't get this XML stuff working I will have to switch to a relational model instead and process the code on the frontend (.NET).

Thanks in Advance,
-dpx Received on Fri Aug 11 2006 - 12:03:02 CDT