Oracle 18c中简洁连接不同路径XML提取查询的方法

不需要强行把两类不同XML路径的解析逻辑塞到同一个xmltable调用里，最简洁、可维护性最高的写法就是用CTE（公用表表达式）分别封装两类数据的提取逻辑，再通过关联字段做匹配，写法比嵌套子查询更清晰，性能也没有损失。

要注意两类数据本身存储在gdb_items_vw视图的不同行中，根本无法通过单条XML解析同时拿到：

• 域数据存储在i.name为ACTIVITY_ATN_1/ACTIVITY_GCSM_1/ACTIVITY_MS_2的记录的XML字段里
• 子类型数据存储在i.name = 'INFRASTR.BC_EVENTS'的记录的XML字段里

因此关联查询是必须的步骤，用LEFT JOIN是完全合理的实现方式，不存在冗余问题。

可直接运行的参考SQL #

WITH domain_data AS (
    -- 提取域编码、描述等核心数据
    SELECT      
        SUBSTR(i.name, 0, 17) AS domain_name,
        SUBSTR(x.code, 0, 13) AS domain_code,
        SUBSTR(x.description, 0, 35) AS domain_description
    FROM        
        gdb_items_vw i
    CROSS APPLY XMLTABLE(
        '/GPCodedValueDomain2/CodedValues/CodedValue' 
        PASSING XMLTYPE(i.definition)
        COLUMNS
            code        VARCHAR2(255) PATH './Code',
            description VARCHAR2(255) PATH './Name'
        ) x    
    WHERE      
        i.name IN ('ACTIVITY_ATN_1','ACTIVITY_GCSM_1','ACTIVITY_MS_2')
        AND i.name IS NOT NULL
),
subtype_data AS (
    -- 提取子类型编码和关联用的域名字段
    SELECT 
        SUBSTR(x.subtype_code, 0, 12) AS subtype_code,
        SUBSTR(x.subtype_domain, 0, 20) AS subtype_domain
    FROM   
        gdb_items_vw i
    CROSS APPLY XMLTABLE(
        '/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]'
        PASSING XMLTYPE(i.definition)
        COLUMNS
            subtype_code   NUMBER(38,0)   PATH './../../SubtypeCode',
            subtype_domain VARCHAR2(255)  PATH './DomainName'
        ) x
    WHERE  
        i.name IS NOT NULL
        AND i.name = 'INFRASTR.BC_EVENTS'
)
-- 关联匹配得到带subtype_code的最终结果
SELECT
    d.domain_name,
    d.domain_code,
    d.domain_description,
    s.subtype_code
FROM domain_data d
LEFT JOIN subtype_data s
    ON d.domain_name = s.subtype_domain;

写法优化点 #

• 两个CTE只保留最终结果需要的字段，冗余字段（如表名、子类型描述等）直接剔除，减少XML解析开销和数据传输量
• 选择LEFT JOIN是为了避免某个域未匹配到对应子类型时，域数据被意外过滤；如果可以确定所有涉及的域都有对应子类型，换成INNER JOIN执行效率会更高
• 域解析、子类型解析、结果关联三个逻辑完全拆分，后续调整任意一部分逻辑都不会影响其他部分，维护成本远低于强行把多段XML解析嵌套在同一个查询块的写法

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