使用Oracle SQL生成嵌套XML

Question

朋友们，

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

<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

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.

我尝试使用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，但它没有显示正确的元素名称。

非常感谢任何能够帮助解决这个问题的人。

提前致谢。

- Ian Carpenter

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

- gpeche

---

非常感谢。我放弃了缩放子查询路线（愚蠢地没有尝试！），因为我认为我会返回多行，但当然在xmlagg中并非如此！+1和答案。 - Ian Carpenter