如何在Oracle单XML查询中同时包含DEPARTMENT与EMPLOYEES节点

需求说明 #

需要生成包含DEPARTMENT和EMPLOYEES节点的XML文档，要求DEPARTMENT节点位于EMPLOYEES之前，将两个独立的Oracle查询合并为一个查询实现。

现有EMPLOYEES查询 #

SELECT
    XMLElement("EMPLOYEES",
    XMLAGG(XMLELEMENT(
        "USER", XMLFOREST(empno AS "ID", ename AS "NAME")
    )))
FROM
    emp where rownum < 3

返回结果：

<EMPLOYEES>
    <USER>
        <ID>7839</ID>
        <NAME>KING</NAME>
    </USER>
    <USER>
        <ID>7698</ID>
        <NAME>BLAKE</NAME>
    </USER>
</EMPLOYEES>

现有DEPARTMENT查询 #

SELECT
    XMLElement("DEPARTMENT",
    XMLAGG(XMLELEMENT(
        "DEPT", XMLFOREST(deptno AS "ID", dname AS "NAME")
    )))
FROM
    dept where rownum < 3

返回结果：

<DEPARTMENT>
    <DEPT>
        <ID>10</ID>
        <NAME>ACCOUNTING</NAME>
    </DEPT>
    <DEPT>
        <ID>20</ID>
        <NAME>RESEARCH</NAME>
    </DEPT>
</DEPARTMENT>

可行解决方案 #

方法1：UNION ALL + XMLAGG控制顺序 #

通过UNION ALL合并两个查询的XML结果，添加排序字段确保DEPARTMENT节点优先，最后用XMLAGG拼接成完整XML。

SELECT XMLAGG(xml_data ORDER BY sort_order) AS combined_xml
FROM (
    -- 生成DEPARTMENT节点，排序值设为1保证在前
    SELECT
        XMLElement("DEPARTMENT",
        XMLAGG(XMLELEMENT(
            "DEPT", XMLFOREST(deptno AS "ID", dname AS "NAME")
        ))) AS xml_data,
        1 AS sort_order
    FROM dept 
    WHERE rownum < 3

    UNION ALL

    -- 生成EMPLOYEES节点，排序值设为2
    SELECT
        XMLElement("EMPLOYEES",
        XMLAGG(XMLELEMENT(
            "USER", XMLFOREST(empno AS "ID", ename AS "NAME")
        ))) AS xml_data,
        2 AS sort_order
    FROM emp 
    WHERE rownum < 3
);

方法2：子查询直接组合XML元素 #

在主查询中调用两个子查询生成对应节点，可选择包裹根节点或直接拼接，结构更直观。

带根节点的写法

SELECT
    XMLElement("ROOT",
        (SELECT
            XMLElement("DEPARTMENT",
            XMLAGG(XMLELEMENT(
                "DEPT", XMLFOREST(deptno AS "ID", dname AS "NAME")
            )))
        FROM dept WHERE rownum < 3),
        (SELECT
            XMLElement("EMPLOYEES",
            XMLAGG(XMLELEMENT(
                "USER", XMLFOREST(empno AS "ID", ename AS "NAME")
            )))
        FROM emp WHERE rownum < 3)
    ) AS combined_xml
FROM dual;

直接拼接无根节点的写法

SELECT
    XMLCONCAT(
        (SELECT
            XMLElement("DEPARTMENT",
            XMLAGG(XMLELEMENT(
                "DEPT", XMLFOREST(deptno AS "ID", dname AS "NAME")
            )))
        FROM dept WHERE rownum < 3),
        (SELECT
            XMLElement("EMPLOYEES",
            XMLAGG(XMLELEMENT(
                "USER", XMLFOREST(empno AS "ID", ename AS "NAME")
            )))
        FROM emp WHERE rownum < 3)
    ) AS combined_xml
FROM dual;

方法特点 #

• 方法1适合后续需要添加更多节点、灵活调整顺序的场景，修改sort_order即可控制节点排列。
• 方法2结构清晰，代码更易读，适合固定节点数量的需求。

内容的提问来源于stack exchange，提问作者user_odoo