From: Anthony KRIZMANIC <Anthony.KRIZMANIC@ceram.fr>
Subject: Nested Cursor statements to generate XML with more than one level hierarchy
Date: 2000/07/07
Message-ID: <39660CBD.2C70@ceram.fr>#1/1
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@ws41.cines.fr 
X-Trace: ws41.cines.fr 963010760 392 192.44.79.66 (7 Jul 2000 22:59:20 GMT)
Organization: MBDS-CERAM
Mime-Version: 1.0
Reply-To: Anthony.KRIZMANIC@ceram.fr
NNTP-Posting-Date: 7 Jul 2000 22:59:20 GMT
Newsgroups: comp.databases.oracle.tools


Hello,

I've got the following purely relational schema :

EMP, DEPT (same as in demobld.sql)
and
PROJECTS (PROJNUM, PROJNAME)
EMP_PROJECTS (PROJNUM, EMPNUM)

I would like to get via XSU an XML with this two level hierarchy :
+ DEPT
	+ PROJECT
		+ EMPLOYEE

But, I'm meeting troubles with nested Cursor Statements.

I succeeded to get the following one level hierarchies (i.e. only one
Cursor Statement) :

+ DEPT
	+ PROJECT

Select DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC,
 Cursor ( Select Distinct EMP_PROJECTS.PROJNUM, PROJECTS.PROJNAME
	  From EMP_PROJECTS, PROJECTS, EMP 
	  Where EMP.DEPTNO = DEPT.DEPTNO And EMP.EMPNO = EMP_PROJECTS.EMPNUM
And EMP_PROJECTS.PROJNUM = PROJECTS.PROJNUM )
From DEPT;


+ PROJECT
	+ EMPLOYEE

Select PROJECTS.PROJNUM, PROJECTS.PROJNAME,
 Cursor ( Select EMP.EMPNO, EMP.ENAME, EMP.JOB
	  From EMP, EMP_PROJECTS
	  Where EMP.EMPNO = EMP_PROJECTS.EMPNUM And EMP_PROJECTS.PROJNUM =
PROJECTS.PROJNUM )
From PROJECTS;


But, I can't get properly the two level hierarchy 

+ DEPT
	+ PROJECT
		+ EMPLOYEE

I wrote this SQL statement :

Select DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC,
 Cursor ( Select Distinct EMP_PROJECTS.PROJNUM, PROJECTS.PROJNAME,
  Cursor ( Select Distinct EMP2.EMPNO, EMP2.ENAME, EMP2.JOB
	   From EMP EMP2, EMP_PROJECTS EMP_PROJECTS2
	   Where EMP2.EMPNO = EMP_PROJECTS.EMPNUM And EMP_PROJECTS2.PROJNUM =
PROJECTS.PROJNUM )
	  From EMP_PROJECTS, PROJECTS, EMP 
	  Where EMP.DEPTNO = DEPT.DEPTNO And EMP.EMPNO = EMP_PROJECTS.EMPNUM
And EMP_PROJECTS.PROJNUM = PROJECTS.PROJNUM )
From DEPT;

But It does not work properly : the project information ARE REPEATED as
often as many there are employee working on the project.

Example :

+ DEPT 10
	+ PROJECT A
		+ KING
	+ PROJECT A	<= project information repeated
		+ CLARK
	+ PROJECT A	<= project information repeated
		+ MILLER

Of course, I would prefer this hierarchy :

+ DEPT 10
	+ PROJECT A
		+ KING
		+ CLARK
		+ MILLER

Does anyone may tell me what is wrong in my SQL statement ?

Does anyone may tell me if he managed to do a N (with N > 1) level
hierarchy (i.e. successfully done nested Cursor Statements) ?

Thanks a lot !