Oracle中如何从存储为CLOB的XML内提取同一标签的多个值

问题根因 #

你原写法的XPath仅定位到ConsumerDetails这一个父节点，直接取子节点EmailAddress/ContactNumber时默认只会返回第一个匹配的标签值，自然无法拿到多组重复标签的内容。 #

方案1：单独提取所有邮箱/所有电话 #

如果只需要分别获取全量邮箱或全量电话，直接把XPath定位到重复的标签本身即可：

提取所有邮箱

SELECT
    em.email_address
FROM
    xml_message_table t, -- 替换为你实际的表名
    XMLTABLE(
        XMLNAMESPACES(DEFAULT 'http://www.ford.com/oagis'),
        'SyncConsumer/DataArea/Consumer/ConsumerDetails/EmailAddress'
        PASSING XMLTYPE(t.orig_message)
        COLUMNS
            email_address VARCHAR2(100) PATH '.' -- "."代表取当前节点的文本值
    ) em;

提取所有电话

SELECT
    cn.contact_number
FROM
    xml_message_table t, -- 替换为你实际的表名
    XMLTABLE(
        XMLNAMESPACES(DEFAULT 'http://www.ford.com/oagis'),
        'SyncConsumer/DataArea/Consumer/ConsumerDetails/ContactNumber'
        PASSING XMLTYPE(t.orig_message)
        COLUMNS
            contact_number VARCHAR2(30) PATH '.'
    ) cn;

方案2：合并输出所有联系方式（带类型标识） #

如果需要把邮箱和电话放在同一个结果集里，区分类型返回：

SELECT
    '邮箱' AS contact_type,
    em.contact_value
FROM
    xml_message_table t,
    XMLTABLE(
        XMLNAMESPACES(DEFAULT 'http://www.ford.com/oagis'),
        'SyncConsumer/DataArea/Consumer/ConsumerDetails/EmailAddress'
        PASSING XMLTYPE(t.orig_message)
        COLUMNS
            contact_value VARCHAR2(100) PATH '.'
    ) em
UNION ALL
SELECT
    '电话' AS contact_type,
    cn.contact_value
FROM
    xml_message_table t,
    XMLTABLE(
        XMLNAMESPACES(DEFAULT 'http://www.ford.com/oagis'),
        'SyncConsumer/DataArea/Consumer/ConsumerDetails/ContactNumber'
        PASSING XMLTYPE(t.orig_message)
        COLUMNS
            contact_value VARCHAR2(30) PATH '.'
    ) cn;

方案3：按顺序关联邮箱和电话 #

如果需要按标签在XML中的出现顺序一一对应（第一个邮箱对应第一个电话，以此类推），可以通过行号关联：

WITH all_emails AS (
    SELECT
        t.主键字段 AS msg_id, -- 替换为你表的主键，用于区分不同XML记录
        em.email_address,
        ROW_NUMBER() OVER(PARTITION BY t.主键字段 ORDER BY 1) AS rn
    FROM
        xml_message_table t,
        XMLTABLE(
            XMLNAMESPACES(DEFAULT 'http://www.ford.com/oagis'),
            'SyncConsumer/DataArea/Consumer/ConsumerDetails/EmailAddress'
            PASSING XMLTYPE(t.orig_message)
            COLUMNS
                email_address VARCHAR2(100) PATH '.'
        ) em
),
all_phones AS (
    SELECT
        t.主键字段 AS msg_id,
        cn.contact_number,
        ROW_NUMBER() OVER(PARTITION BY t.主键字段 ORDER BY 1) AS rn
    FROM
        xml_message_table t,
        XMLTABLE(
            XMLNAMESPACES(DEFAULT 'http://www.ford.com/oagis'),
            'SyncConsumer/DataArea/Consumer/ConsumerDetails/ContactNumber'
            PASSING XMLTYPE(t.orig_message)
            COLUMNS
                contact_number VARCHAR2(30) PATH '.'
        ) cn
)
SELECT
    e.email_address,
    p.contact_number
FROM
    all_emails e
FULL OUTER JOIN all_phones p 
    ON e.msg_id = p.msg_id 
    AND e.rn = p.rn;

如果邮箱和电话数量不一致，全外连接会保留多出的部分，缺失字段值为null。

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