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 entry selected on the outer line, and diving should contain all the other entries, except the one shown on the outer line. There is no hierarchy for records.
In the above example, the king is selected in the outer line, so in the exploits should be all records from emp, excluding King.
This query gives me the desired result:
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 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
A whist that outputs XML in the expected format, it does not display the correct element names.
Any help in resolving this issue would be greatly appreciated.
Thanks in advance