sql - 使用 Oracle SQL 生成嵌套 XML

Question

friend 们，

我正在使用 Oracle 10g，我需要使用以下 xml 格式的 SQL 从表中生成结果:

<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>

规则是显示在外行中选择的记录，子行应包含除外行中显示的记录之外的所有其他记录。记录没有层次结构。

在上面的示例中，在外行中选择了 King，因此子行应包含来自 emp 的所有记录，但不包括 King。

这个查询给了我我需要的结果集:

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

但是，当我尝试使用以下方法从中生成 XML 时:

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

我收到以下错误:

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.

我曾尝试使用 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

虽然这以预期的格式输出 XML，但它没有显示正确的元素名称。

任何帮助解决这个问题将不胜感激。

提前致谢

Answer 1

这行不通？

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