Re: Appending XMLELEMENTs to existing XMLType

dataplex@xxxxxxxxx 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
.

Relevant Pages

  • Appending XMLELEMENTs to existing XMLType
    ... I have a self referencing table that I'm trying to build an XML ... SELECT xmlelement("Categories", ... what I want is for the self-referencing hierarchy to be ... catXML = concatenate processCategory ...
    (comp.databases.oracle.server)
  • Re: DISCOVER_XML_METADATA
    ... Small XML is not problem. ... and then run following for each database: ... unprocessed cubes and it doesn't show processing status information ... A DBSCHEMA_CATALOGS query will get you a list of all the databases ...
    (microsoft.public.sqlserver.olap)
  • RE: Query producing XML appears to be cached
    ... By default the XMLDataSource control always caches its data, ... I have an app that executed a sql server query that produces an XML ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Typed XML slows down query?!
    ... > Hi Martin, ... >>I have a performance problem with typed XML and a simple query. ... >> CREATE PRIMARY XML INDEX idx_article ...
    (microsoft.public.sqlserver.xml)
  • setting dataset datarelation from database
    ... I want to set my dataset's datarelations based on the relationships ... I had a query I ... switched to using 'for xml explicit, ... If I run the query in sql query analyzer, I see the schema ...
    (microsoft.public.dotnet.framework.adonet)
Loading