Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using Oracle SQL to generate nested XML

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

like image 810
Ian Carpenter Avatar asked Nov 21 '10 19:11

Ian Carpenter


People also ask

Does Oracle support XML?

Oracle XML DB provides full support for all of the key XML standards, including XML, Namespaces, DOM, XQuery, SQL/XML and XSLT. By providing full support for XML standards, Oracle XML DB supports native XML application development.

What is nested subquery in Oracle?

A subquery nested in the WHERE clause of the SELECT statement is called a nested subquery. A subquery can contain another subquery. Oracle allows you to have an unlimited number of subquery levels in the FROM clause of the top-level query and up to 255 subquery levels in the WHERE clause.

What is XMLElement in SQL?

XMLElement takes an element name for identifier , an optional collection of attributes for the element, and arguments that make up the content of the element. It returns an instance of type XMLType .


1 Answers

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
like image 79
gpeche Avatar answered Oct 06 '22 04:10

gpeche