当然可以！Oracle提供了好几种原生方法来生成包含嵌套数组（非原子数据）的结果集，完美适配你要写入嵌套XML的需求。下面我会分享几个最常用的实现方式：

方法1：直接用XMLAGG+XMLELEMENT构建嵌套XML结构 #

如果你的目标就是生成嵌套XML，其实可以一步到位，不用先拿嵌套数据集再转格式。Oracle的XML函数能直接在查询里构建层级结构：

比如假设你有主表orders和从表order_items，要生成包含订单下所有商品的嵌套XML：

SELECT XMLELEMENT("Order",
           XMLATTRIBUTES(o.order_id AS "ID"),
           XMLELEMENT("Customer", o.customer_name),
           XMLAGG(
             XMLELEMENT("Item",
               XMLATTRIBUTES(oi.item_id AS "ItemID"),
               XMLELEMENT("Product", oi.product_name),
               XMLELEMENT("Quantity", oi.quantity)
             )
           ) AS "Items"
         ) AS nested_xml
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.customer_name;

这个查询会直接返回包含嵌套<Items>节点的XML结果，每个订单对应一组商品数组，正好满足你的需求。

方法2：先生成JSON嵌套数组，再转XML #

如果你需要先拿到嵌套数组形式的数据集，之后再处理成XML，可以用JSON_ARRAYAGG生成JSON格式的嵌套数组，再通过XMLTYPE转换：

SELECT o.order_id,
       o.customer_name,
       -- 得到包含嵌套对象的数组单元格
       JSON_ARRAYAGG(
         JSON_OBJECT(
           'item_id' VALUE oi.item_id,
           'product_name' VALUE oi.product_name,
           'quantity' VALUE oi.quantity
         )
       ) AS items_array,
       -- 直接转换为嵌套XML结构
       XMLTYPE(
         JSON_OBJECT(
           'order_id' VALUE o.order_id,
           'customer_name' VALUE o.customer_name,
           'items' VALUE JSON_ARRAYAGG(
             JSON_OBJECT(
               'item_id' VALUE oi.item_id,
               'product_name' VALUE oi.product_name,
               'quantity' VALUE oi.quantity
             )
           )
         )
       ) AS nested_xml
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.customer_name;

这里的items_array列就是你要的嵌套数据单元格，后续可以轻松转成目标XML格式。

方法3：用自定义嵌套表类型实现复杂嵌套 #

如果需要更灵活的复杂嵌套结构，可以先定义自定义数据类型和嵌套表，再通过查询填充：

1. 先创建对象类型和嵌套表类型：

CREATE TYPE order_item_type AS OBJECT (
  item_id NUMBER,
  product_name VARCHAR2(100),
  quantity NUMBER
);
/
CREATE TYPE order_items_table AS TABLE OF order_item_type;
/

2. 然后用CAST+COLLECT生成嵌套表：

SELECT o.order_id,
       o.customer_name,
       CAST(COLLECT(order_item_type(oi.item_id, oi.product_name, oi.quantity)) AS order_items_table) AS items_table
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.customer_name;

这种方式得到的items_table是真正的嵌套表类型，你可以在PL/SQL中进一步处理，或者直接转成XML：

SELECT XMLELEMENT("Order",
           XMLATTRIBUTES(o.order_id AS "ID"),
           XMLELEMENT("Customer", o.customer_name),
           XMLFOREST(
             CAST(COLLECT(order_item_type(oi.item_id, oi.product_name, oi.quantity)) AS order_items_table) AS "Items"
           )
         ) AS nested_xml
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.customer_name;

版本注意事项 #

• XMLAGG在Oracle 10g及以上就支持了；
• JSON_ARRAYAGG需要Oracle 12c Release 2（12.2）及以上版本；
• 自定义嵌套表类型则是Oracle很早就支持的特性。

你可以根据自己的Oracle版本选择最合适的方式~

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