Using Oracle SQL to generate nested XML

Question

Friends,

I am using Oracle 10g and I need to generate the results from a table using SQL in the following xml format:

<RESULTS>
    <ROW>
        <EMPNO>7839</EMPNO>
        <ENAME>KING</EMPNO>
        <SUBROWS>
            <ROW>
                <EMPNO>7369</EMPNO>
                <ENAME>SMITH</EMPNO>
                ... Rest of the EMP table records
                excluding KING
            </ROW>
        </SUBROWS>
    </ROW>
</RESULTS>

The rule is to show the record selected in the outer row and the subrows should contain all the other records excluding the one shown in the outer row. There is no hierarchy to the records.

In the example above King is selected in the outer row so the subrows should contain all the records from emp excluding King.

This query gives me the result set I need:

select e.empno,
       e.ename,
       cursor(select empno,
                     ename
              from emp where empno <> 7839) 
from emp e 
where empno = 7839

However when I try to generate XML from this using the following:

select xmlelement("RESULTS",
           xmlagg(xmlelement("ROW",
                      xmlelement("EMPNO", empno),
                      xmlelement("ENAME", ename),
                          cursor(SELECT xmlagg(xmlelement("SUBROWS",  xmlelement("ROW",
                                                     xmlelement("EMPNO", empno),
                                                     xmlelement("ENAME", ename)
                                                                                )
                                                           )
                                               )
                                  FROM emp 
                                  WHERE empno <> 7839
                                  )                            
                            )

                 )
                 )                 
from emp 
where empno = 7839

I get the following error:

ORA-22902: CURSOR expression not allowed
22902. 00000 -  "CURSOR expression not allowed"
*Cause:    CURSOR on a subquery is allowed only in the top-level
           SELECT list of a query.

I have tried using DBMS_XMLGEN:

SELECT DBMS_XMLGEN.getXML('select empno,
                                  ename,
                                  cursor(select empno,
                                                ename
                                         from emp 
                                         where empno <> 7839) as SUBROWS
                            from emp 
                            where empno = 7839') 
FROM dual

Whist this outputs XML in the expected format it is doesn't show the correct element names.

Any help in solving this would be very much appreciated.

Thanks in advance

Answer 1

This does not work?

select xmlelement("RESULTS",
       xmlagg(xmlelement("ROW",
                  xmlelement("EMPNO", empno),
                  xmlelement("ENAME", ename),
                      (SELECT xmlagg(xmlelement("SUBROWS",  xmlelement("ROW",
                                                 xmlelement("EMPNO", empno),
                                                 xmlelement("ENAME", ename)
                                                                            )
                                                       )
                                           )
                              FROM emp 
                              WHERE empno <> 7839
                              )                            
                        )

             )
             )                 
from emp 
where empno = 7839