Oracle SQL聚合XML子标签值的问题及优化需求

需求说明 #

需要将XML中子标签<Statement>的值聚合到Oracle SQL的列中，例如将Id="16"的<Statements>下的所有<Statement>值合并为Subtype4,Subtype5,Subtype10格式。

原XML结构示例：

<Product>
[..other tags..]
 <Attributes>
   <Statements Id="1" Name="Statement 0">
     <Statement Id="4">Subtype 1</Statement>
   </Statements>
   <Statements Id="3" Name="Statement 1">
     <Statement Id="4">Subtype 4</Statement>
     <Statement Id="5">Subtype 5</Statement>
     <Statement Id="15">Subtype 15</Statement>
   </Statements>
   <Statements Id="16" Name="Statement 2">
     <Statement Id="4">Subtype 4</Statement>
     <Statement Id="5">Subtype 5</Statement>
     <Statement Id="10">Subtype 10</Statement>
   </Statements>
 </Attributes>
</Product>

原查询存在的问题：

• 多<Statements>标签场景下性能差，多次CROSS JOIN导致数据膨胀
• 连接机制导致后续Statement值重复N次（N为Statement1列的条目数）

优化后的查询语句 #

SELECT 
    b.id,
    -- 聚合Id=3的Statements下的Statement值
    (SELECT LISTAGG(s.text_content, ',') WITHIN GROUP (ORDER BY NULL)
     FROM XMLTABLE('/Product/Attributes/Statements[@Id="3"]/Statement'
                  PASSING XMLTYPE(b.XML_DOCUMENT)
                  COLUMNS text_content VARCHAR2(100) PATH 'text()')) AS statement_1,
    -- 聚合Id=16的Statements下的Statement值
    (SELECT LISTAGG(s.text_content, ',') WITHIN GROUP (ORDER BY NULL)
     FROM XMLTABLE('/Product/Attributes/Statements[@Id="16"]/Statement'
                  PASSING XMLTYPE(b.XML_DOCUMENT)
                  COLUMNS text_content VARCHAR2(100) PATH 'text()')) AS statement_2
FROM 
    TABLE_WHERE_XML_RESIDES b;

优化说明 #

• 避免笛卡尔积：使用关联子查询代替多次CROSS JOIN，每个聚合列独立计算，彻底解决重复值问题
• 简化XML解析：直接定位到目标<Statement>节点，无需嵌套XMLTABLE，减少解析层级，提升性能
• 逻辑易扩展：后续需要聚合其他Statements标签时，只需复制对应子查询块并修改@Id属性值即可

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