使用 Oracle SQL 生成嵌套 XML
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4239972/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Oracle SQL to generate nested XML
提问by Ian Carpenter
Friends,
朋友们，
I am using Oracle 10g and I need to generate the results from a table using SQL in the following xml format:
我正在使用 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>
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.
在上面的示例中，在外行中选择了 King，因此子行应包含来自 emp 的所有记录，但不包括 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:
但是，当我尝试使用以下方法从中生成 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.
I have tried using DBMS_XMLGEN:
我曾尝试使用 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.
虽然这以预期的格式输出 XML，但它没有显示正确的元素名称。
Any help in solving this would be very much appreciated.
任何帮助解决这个问题将不胜感激。
Thanks in advance
提前致谢
回答by gpeche
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
