Oracle CLOB字段XML结构化提取SQL优化求助

问题场景 #

Oracle表TEST_LUXAIR_TEMP_LOAD_PROD.CUSTOM_XML_CLIENT_DATA的xml_content字段（CLOB类型）存储了层级XML数据，需要提取messageSender、requestID、additionalCode及其对应的referenceNumber，避免出现笛卡尔积式的错误关联。

错误原因 #

原SQL语句将所有additionalCode节点与所有referenceNumber节点直接关联，导致每个additionalCode匹配所有referenceNumber，形成笛卡尔积，无法保持XML的层级关系。

正确SQL语句（Oracle 12c+） #

使用OUTER APPLY实现层级关联，同时保留无requestedDocuments的行：

SELECT
    cct.message_sender,
    cct.request_id,
    rai.additional_code,
    rd.reference_number
FROM
    TEST_LUXAIR_TEMP_LOAD_PROD.CUSTOM_XML_CLIENT_DATA t,
    -- 提取顶层节点信息
    XMLTable('/CCTS063A'
        PASSING XMLType(t.xml_content)
        COLUMNS
            message_sender VARCHAR2(100) PATH 'messageSender',
            request_id VARCHAR2(10) PATH 'RequestDetails/requestID',
            rai_nodes XMLType PATH 'RequestDetails/requestedAdditionalInformation'
    ) cct,
    -- 遍历每个requestedAdditionalInformation节点
    XMLTable('/requestedAdditionalInformation'
        PASSING cct.rai_nodes
        COLUMNS
            additional_code VARCHAR2(10) PATH 'additionalCode',
            rd_nodes XMLType PATH 'requestedDocuments'
    ) rai,
    -- 关联当前additionalCode下的requestedDocuments，无则返回NULL
    OUTER APPLY (
        SELECT reference_number
        FROM XMLTable('/requestedDocuments'
            PASSING rai.rd_nodes
            COLUMNS
                reference_number VARCHAR2(70) PATH 'referenceNumber'
        )
        UNION ALL
        SELECT NULL FROM DUAL WHERE rai.rd_nodes IS NULL
    ) rd;

兼容低版本Oracle的写法（无需OUTER APPLY） #

如果使用Oracle 12c之前的版本，可通过UNION ALL合并有/无referenceNumber的结果：

-- 提取有requestedDocuments的行
SELECT
    main.message_sender,
    main.request_id,
    rai.additional_code,
    rd.reference_number
FROM
    TEST_LUXAIR_TEMP_LOAD_PROD.CUSTOM_XML_CLIENT_DATA t,
    XMLTable('/CCTS063A'
        PASSING XMLType(t.xml_content)
        COLUMNS
            message_sender VARCHAR2(100) PATH 'messageSender',
            request_details XMLType PATH 'RequestDetails'
    ) main,
    XMLTable('/RequestDetails'
        PASSING main.request_details
        COLUMNS
            request_id VARCHAR2(10) PATH 'requestID',
            rai_nodes XMLType PATH 'requestedAdditionalInformation'
    ) rd_main,
    XMLTable('/requestedAdditionalInformation'
        PASSING rd_main.rai_nodes
        COLUMNS
            additional_code VARCHAR2(10) PATH 'additionalCode',
            rd_nodes XMLType PATH 'requestedDocuments'
    ) rai,
    XMLTable('/requestedDocuments'
        PASSING rai.rd_nodes
        COLUMNS
            reference_number VARCHAR2(70) PATH 'referenceNumber'
    ) rd
UNION ALL
-- 提取无requestedDocuments的行
SELECT
    main.message_sender,
    main.request_id,
    rai.additional_code,
    NULL AS reference_number
FROM
    TEST_LUXAIR_TEMP_LOAD_PROD.CUSTOM_XML_CLIENT_DATA t,
    XMLTable('/CCTS063A'
        PASSING XMLType(t.xml_content)
        COLUMNS
            message_sender VARCHAR2(100) PATH 'messageSender',
            request_details XMLType PATH 'RequestDetails'
    ) main,
    XMLTable('/RequestDetails'
        PASSING main.request_details
        COLUMNS
            request_id VARCHAR2(10) PATH 'requestID',
            rai_nodes XMLType PATH 'requestedAdditionalInformation'
    ) rd_main,
    XMLTable('/requestedAdditionalInformation'
        PASSING rd_main.rai_nodes
        COLUMNS
            additional_code VARCHAR2(10) PATH 'additionalCode',
            rd_nodes XMLType PATH 'requestedDocuments'
    ) rai
WHERE
    rai.rd_nodes IS NULL;

执行结果 #

message_sender  request_id  additional_code  reference_number
TS.LU           3           A10              (null)
TS.LU           3           A20              helloworld_max70_chars_1
TS.LU           3           A20              helloworld_max70_chars_2
TS.LU           3           A30              helloworld_max70_chars_3
TS.LU           3           A30              helloworld_max70_chars_4
TS.LU           3           A30              helloworld_max70_chars_5
TS.LU           3           A40              (null)

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