Home » SQL & PL/SQL » SQL & PL/SQL » Need to generate hierarchical recursive XML using SQL
Need to generate hierarchical recursive XML using SQL [message #192781] Wed, 13 September 2006 09:28 Go to next message
viral303
Messages: 2
Registered: September 2006
Junior Member
create table emp ( empname varchar2(10),
mgrname varchar2(10),
constraint emp_pk primary key (empname));


insert into emp values ('KING', 'JACK');
insert into emp values ('LUKE', 'JACK');
insert into emp values ('MEL', 'LUKE');
insert into emp values ('JACK', null);

commit;

-- data in the table

select * from emp;

EMPNAME MGRNAME
---------- ----------
KING JACK
LUKE JACK
MEL LUKE
JACK

-- hierarchical traditional output

select level,mgrname,empname
from emp
start with empname = 'JACK'
connect by prior empname = mgrname;

LEVEL MGRNAME EMPNAME
---------- ---------- ----------
1 JACK
2 JACK KING
2 JACK LUKE
3 LUKE MEL



-- Requirement for desired output is xmltype with following output

<emp name="JACK">
<emp name="KING"></emp>
<emp name="LUKE">
<emp name="MEL"></emp>
</emp>
</emp>
Re: Need to generate hierarchical recursive XML using SQL [message #192932 is a reply to message #192781] Thu, 14 September 2006 04:54 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Which version of the database are you on?

In 10G, the function dbms_xmlgen.newcontextFromHierarchy can be used - this takes a hierarchical query string as input and returns a context which can generate XML with recursive elements. See the documentation here.
Re: Need to generate hierarchical recursive XML using SQL [message #193080 is a reply to message #192932] Thu, 14 September 2006 16:28 Go to previous messageGo to next message
viral303
Messages: 2
Registered: September 2006
Junior Member
Thanks for pointing me to right direction.

Oracle version is 10.2.

This sample would work with the approach in the doc., however I have a pl/sql object table instead of a real table.

i.e. instead of emp it is a sql type object table of a sql type.

So, the query is would be, and where emp is a pl/sql variable, I'm not sure how to pass that variable

select ...,
....
from table(emp)
where ...
start with ...
connect by ...


I have tried to pass a dynamic query with using clause in the
DBMS_XMLGEN.newcontextFromHierarchy, but that did not work.
Re: Need to generate hierarchical recursive XML using SQL [message #193918 is a reply to message #192781] Tue, 19 September 2006 23:21 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Does the query work independently? I don't have access to a 10G database to try this out, but you can verify:
- if the query is valid
- returns 2 columns, the first being the level in the hierarchy, the second an XMLType value

If this doesn't help, post the relevant code and the exact error...
Previous Topic: In vs Exists
Next Topic: String Formatting
Goto Forum:
  


Current Time: Fri Dec 09 21:38:46 CST 2016

Total time taken to generate the page: 0.09891 seconds