SQL: Is it possible to hava master-detail queries with the sql-pr ocessor?

[Henning von Bargen <H....@Triestram-Partner.de>]

With the XSQL utility from Oracle (see http://technet.oracle.com)
it is possible to generate structured XML documents from master-detail
queries,
but this is done using a special feature of Oracle 8 
(subquery: a column that is actually only a reference to a cursor)

I wonder if similar functionality can be achieved with the Cocoon SQL
processor.
Think of the good old DEPT/EMP scenario:
Table DEPT describes departments, table EMP describes employees.

Now we want to list all departments together with the corresponding
employees
in a structured way like this:

<ROWSET>
  <ROW ID="1">
    <DNAME>Research</DNAME>
    <LOC>Somewhere out there</LOC>
    <EMP_ROWSET>
       <EMP_ROW ID="1">
         <ENAME>Smith</ENAME>
         <HIREDATE>01/01/1980</HIREDATE>
      </EMP_ROW>
       <EMP_ROW ID="2">
         <ENAME>Jones</ENAME>
         <HIREDATE>04/08/1981</HIREDATE>
      </EMP_ROW>
   </ROW>
  <ROW ID="1">
    <DNAME>Sales</DNAME>
    <LOC>Right here</LOC>
    <EMP_ROWSET>
       <EMP_ROW ID="1">
         <ENAME>Parker</ENAME>
         <HIREDATE>01/01/1982</HIREDATE>
      </EMP_ROW>
       <EMP_ROW ID="2">
         <ENAME>Gates</ENAME>
         <HIREDATE>04/08/1983</HIREDATE>
      </EMP_ROW>
   </ROW>
</ROWSET>

Using the usual join operator, 

select	dept.dname
,	dept.loc
,	emp.ename
,	emp.hiredate
from	dept, emp
	where emp.dname (+) = dept.dname;

where (+) is the oracle syntax for an outer-join,
we'll get ONE flat rowset:

<ROWSET>
  <ROW ID="1">
    <DNAME>Research</DNAME>
    <LOC>Somewhere out there</LOC>
    <ENAME>Smith</ENAME>
    <HIREDATE>01/01/1980</HIREDATE>
  </ROW>
  <ROW ID="2">
    <DNAME>Research</DNAME>
    <LOC>Somewhere out there</LOC>
    <ENAME>Jones</ENAME>
     <HIREDATE>04/08/1981</HIREDATE>
   </ROW>
  <ROW ID="3">
    <DNAME>Sales</DNAME>
    <LOC>Right here</LOC>
    <ENAME>Parker</ENAME>
    <HIREDATE>01/01/1982</HIREDATE>
  </ROW>
  <ROW ID="4">
    <DNAME>Sales</DNAME>
    <LOC>Right here</LOC>
     <ENAME>Gates</ENAME>
     <HIREDATE>04/08/1983</HIREDATE>
   </ROW>
</ROWSET>

This is not what we want.
Using XSLT we can get what we want, but that's not
the right solution: Maybe the DEPT table has some very long data columns
which we also want to select and display.
With the above query, all these columns are repeated for every employee,
thus generating a huge XML file which, in addition, is not structured the
way we want.

What we need is something like

 <query connection="foo_connection">
  select deptno
  ,	dname
  ,         loc
  from dept order by dname
   <query connection="foo_connection">
      select ename from emp where emp.deptno=dept.dname 
      -- Broken here, actually we need the DEPTNO-value of the current
DEPT-row
   </query>
 </query>

The above code does not work, even if we replace dept.dname with a fixed
value.

Is there a solution?

Thanks,
Henning