Re: Appending XMLELEMENTs to existing XMLType

dataplex_at_gmail.com schrieb:
> 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>
> <Category id="2" name="ParentCat1">
> <Category id="6" name="ParentCat1Sub1" />
> </Category>
> <Category id="3" name="ParentCat2" />
> ....
> </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
>

Not aware of pure SQL solution, but if you can resort to pl sql, see, if this helps:
http://groups.google.de/group/comp.databases.oracle.misc/browse_thread/thread/570fd2c51bd1b52a/f37609fc36912dd8?lnk=gst&q=maxim+xml&rnum=1#f37609fc36912dd8

To append xml child nodes, one will usually use http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions005.htm#CIHEGIFE or
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions066.htm#CIHIJEBB

Said that, i assumed, your Oracle version is uptodate, but to save me time to make assumption, feel free to tell in the future your Oracle version - this kind of functionality changes very fast from release to release. ( also search on this newsgroup or in OTN XMLDB Forum may help a lot).

Best regards

Maxim Received on Fri Aug 11 2006 - 13:04:39 CDT