Nested Cursor statements to generate XML with more than one level hierarchy

From: Anthony KRIZMANIC <Anthony.KRIZMANIC_at_ceram.fr>
Date: 2000/07/07
Message-ID: <39660CBD.2C70_at_ceram.fr>#1/1

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 ! Received on Fri Jul 07 2000 - 00:00:00 CEST