如何编写SQL从Oracle表CLOB列的SOAP XML中提取全部邮箱地址

假设你存储XML的CLOB列名为xml_content，目标存储邮箱的表为user_email，表结构包含email_addr VARCHAR2(255)字段，可直接使用以下INSERT语句完成需求：

INSERT INTO user_email (email_addr)
SELECT xt.email
FROM xml_message xm,
     XMLTable(
       XMLNAMESPACES(
         'http://gateway.consumerhub.commercial.volvocars.net/oagis' AS "oag"
       ),
       '//oag:EmailAddress/text()'
       PASSING XMLType(xm.xml_content)
       COLUMNS
         email VARCHAR2(255) PATH '.'
     ) xt
-- 可选：添加过滤条件筛选xml_message表中需要处理的记录
-- WHERE xm.id = xxx AND xm.create_time >= DATE'2024-01-01'
;

要点说明 #

• 命名空间声明：必须和XML中定义的oag前缀对应的URI完全一致，否则XPath无法匹配到对应节点，本方案中URI直接取自你提供的样例XML头部定义。
• 多节点适配：XPath表达式//oag:EmailAddress会匹配XML中任意层级的所有该类型节点，不管节点嵌套深度、数量多少，都可以全部提取。
• 异常适配：如果存在部分CLOB内容不是合法XML的情况，可以在WHERE条件中添加XMLIsValid(XMLType(xm.xml_content)) = 1过滤掉非法XML记录，避免语句执行报错。
• 属性提取扩展：如果需要同时提取EmailAddress节点的Active、Usage等属性，可修改XMLTable的COLUMNS部分，示例如下：

-- 同时提取邮箱和对应属性的示例
SELECT xt.email, xt.active_status, xt.usage_type
FROM xml_message xm,
     XMLTable(
       XMLNAMESPACES(
         'http://gateway.consumerhub.commercial.volvocars.net/oagis' AS "oag"
       ),
       '//oag:EmailAddress'
       PASSING XMLType(xm.xml_content)
       COLUMNS
         email VARCHAR2(255) PATH './text()',
         active_status VARCHAR2(10) PATH './@Active',
         usage_type VARCHAR2(50) PATH './@Usage'
     ) xt

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