Oracle中EXTRACT提取XMLType节点值返回空的问题咨询

返回空值是因为待解析XML声明了默认命名空间，但查询中的XPath路径未做对应命名空间匹配，解析器无法定位到目标节点。
从你提供的XML样例可以看到，根节点<Document>带有属性：
xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"
这是默认命名空间声明，该节点下所有未单独指定命名空间的子节点（包括你要找的CstmrCdtTrfInitn、GrpHdr、MsgId），全部属于这个命名空间。而你写的XPath路径没有声明该命名空间，解析器只会去匹配「无命名空间」下的同名节点，自然找不到内容返回空。

常见相关误区：

• 误以为只有带前缀的命名空间（比如样例里的xmlns:xsi="xxx"）才需要处理，默认命名空间（不带前缀的xmlns声明）同样会影响节点匹配规则
• 试图用/*/CstmrCdtTrfInitn/...这类模糊路径绕开命名空间，这种写法不仅匹配性能差，遇到重名节点时还会返回错误结果

方案1：修正EXTRACT函数的参数，显式声明命名空间 #

EXTRACT函数支持第三个参数传入命名空间声明，你可以自定义前缀映射目标命名空间，再在XPath中给所有节点加上对应前缀即可：

WITH q1(Tdata,paymentinterchangekey) AS
(
  SELECT XMLtype(transportdata, 1), paymentinterchangekey
    FROM bph_owner.paymentinterchange
   WHERE paymentinterchangekey = '137630105'
)
SELECT 
  -- 自定义ns前缀映射默认命名空间，路径末尾加/text()直接取文本值
  EXTRACT(
    q1.Tdata, 
    '/ns:Document/ns:CstmrCdtTrfInitn/ns:GrpHdr/ns:MsgId/text()', 
    'xmlns:ns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"'
  ).getStringVal() AS msg_id,
  q1.Tdata,
  q1.paymentinterchangekey "EE"
FROM q1;

说明：前缀名可以自定义（比如叫pain、root都可以），只要命名空间声明里的前缀和XPath里用的前缀保持一致即可。

方案2：用XMLTABLE函数解析（官方推荐） #

Oracle早已不推荐使用老旧的EXTRACT函数处理XML，更建议用XMLTABLE，支持直接声明默认命名空间，不需要给每个节点加前缀，同时解析多字段时写法更简洁：

WITH q1(Tdata,paymentinterchangekey) AS
(
  SELECT XMLtype(transportdata, 1), paymentinterchangekey
    FROM bph_owner.paymentinterchange
   WHERE paymentinterchangekey = '137630105'
)
SELECT x.msg_id, x.cre_dt_tm, x.nb_of_txs, q1.paymentinterchangekey "EE"
FROM q1,
XMLTABLE(
  -- 直接声明XML的默认命名空间，后续XPath无需加前缀
  XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:pain.001.001.03'),
  '/Document/CstmrCdtTrfInitn/GrpHdr'
  PASSING q1.Tdata
  COLUMNS
    msg_id    VARCHAR2(100) PATH 'MsgId',
    cre_dt_tm TIMESTAMP     PATH 'CreDtTm',
    nb_of_txs NUMBER        PATH 'NbOfTxs',
    ctrl_sum  NUMBER        PATH 'CtrlSum'
) x;

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