Oracle 19c：从CLOB类型XML中提取数据（SELECT）失败求助

核心问题在于你的XML存在默认命名空间http://www.dual.com，直接使用EXTRACT/EXTRACTVALUE会因为无法识别命名空间下的节点而失败。以下是正确的提取方案：

1. 单个节点值的提取（使用XMLTABLE） #

假设你的表名为your_table，存储XML的CLOB字段名为xml_clob_col，提取indAp、tpInsc、ideEmp下的nrInsc：

SELECT
  xt.ind_ap,
  xt.tp_insc,
  xt.nr_insc_emp
FROM your_table t,
XMLTABLE(
  XMLNAMESPACES(DEFAULT 'http://www.dual.com'), -- 声明默认命名空间
  '/tot/eSl/evtCS'
  PASSING XMLTYPE(t.xml_clob_col) -- 将CLOB转换为XMLTYPE
  COLUMNS
    ind_ap NUMBER PATH 'ideEvt/indAp',
    tp_insc NUMBER PATH 'ideEmp/tpInsc',
    nr_insc_emp VARCHAR2(20) PATH 'ideEmp/nrInsc'
) xt;

2. 多重复节点的提取（嵌套XMLTABLE） #

针对infoCS下多个ideEstab节点的nrInsc，需要嵌套XMLTABLE拆分多行结果：

SELECT
  xt_main.ind_ap,
  xt_main.tp_insc,
  xt_main.nr_insc_emp,
  xt_estab.nr_insc_estab
FROM your_table t,
XMLTABLE(
  XMLNAMESPACES(DEFAULT 'http://www.dual.com'),
  '/tot/eSl/evtCS'
  PASSING XMLTYPE(t.xml_clob_col)
  COLUMNS
    ind_ap NUMBER PATH 'ideEvt/indAp',
    tp_insc NUMBER PATH 'ideEmp/tpInsc',
    nr_insc_emp VARCHAR2(20) PATH 'ideEmp/nrInsc',
    info_cs_xml XMLTYPE PATH 'infoCS' -- 提取infoCS节点作为子XML
) xt_main,
XMLTABLE(
  XMLNAMESPACES(DEFAULT 'http://www.dual.com'),
  'infoCS/ideEstab'
  PASSING xt_main.info_cs_xml
  COLUMNS
    nr_insc_estab VARCHAR2(20) PATH 'nrInsc'
) xt_estab;

关键注意事项 #

• 命名空间必须声明：XML中带有xmlns属性的默认命名空间，必须在查询中通过XMLNAMESPACES指定，否则所有针对该命名空间下节点的路径都会失效。
• 弃用函数替代：EXTRACTVALUE和旧版EXTRACT已被Oracle官方弃用，推荐使用XMLTABLE（最灵活）或XMLCAST(XMLQUERY(...) AS ...)来提取值。
• XML格式验证：确保CLOB中的XML语法完全正确（无未闭合标签、转义错误等），否则XMLTYPE转换会失败。

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