Search code examples
sqloracle-databasehierarchical-query

Hierarchical Query - Capturing the parts of the path

I have a hierarchical query in Oracle 11g that gets me all my leaf nodes out of my tree, and it works great. However, I need to try and capture each part of the path separately while I traverse to my leaf node. Is there some way to do that? either with a modification to my query or a second query??

Any help is really appreciated!

Here is my query:

select c.id, 
   c.superid,
   c.name,
   SYS_CONNECT_BY_PATH(c.name, '>>') as PathName
from mytable c
where c.activestatus =0 AND
  c.id NOT IN(select distinct c2.superid from categorizations c2 where c2.superid IS NOT NULL)
start with c.superid IS NULL
connect by prior c.id = c.superid;

So currently I get an output like: id, superid, name1, >>name1>>name2>>name3>>name

Which is good. But I'm trying to somehow capture each of the node names (because I need to output it in XML).

So right now my XML for each leaf node looks like 

<Node1>
  <Project>name</Project>
  <PathName>>name1>>name2>>name3>>name</PathName>
</Node1>.

I'd like to somehow be able to get XML that looks like 

<Node1>
 <Project>name1
   <Project>name2
      <Project>name3
         <Project>name</Project>
      </Project>
   </Project>
</Project>
<PathName>>name1>>name2>>name3>>name</PathName>
</Node1>

....or something like that, basically I'd like to be able to output my tree hierarchy to XML.

Solution

  • You could use <project> instead of >> prepend another and then use the level pseudo-column to append one </project> per level