如何使用PostgreSQL从表中XML payload提取CreDtTm节点值

你的XML文档包含默认命名空间urn:iso:std:iso:20022:tech:xsd:camt.054.001.08，直接用不带命名空间的XPath查询无法匹配到节点，这就是原SQL语句取不到值的原因。以下是两种可行的解决方法：

方法一：在XPath函数中直接指定命名空间映射 #

SELECT unnest(xpath('/ns:Document/ns:BkToCstmrDbtCdtNtfctn/ns:GrpHdr/ns:CreDtTm/text()', 
                    xml_payload,
                    ARRAY[array['ns', 'urn:iso:std:iso:20022:tech:xsd:camt.054.001.08']])) AS cre_dt_tm
FROM wire.stages;

• 这里用ARRAY[array['ns', '命名空间URI']]定义了命名空间别名ns，后续XPath路径中所有节点都需要加上ns:前缀来关联命名空间。

方法二：先全局设置命名空间 #

-- 先设置命名空间别名
SET xmlnamespace uri 'urn:iso:std:iso:20022:tech:xsd:camt.054.001.08' AS ns;

-- 再执行查询
SELECT unnest(xpath('/ns:Document/ns:BkToCstmrDbtCdtNtfctn/ns:GrpHdr/ns:CreDtTm/text()', xml_payload)) AS cre_dt_tm
FROM wire.stages;

• 这种方式适合需要多次执行同类型XML查询的场景，一次设置后后续查询可直接使用别名。

如果每条记录仅对应一个CreDtTm节点，还可以用xpath_first简化结果：

SELECT xpath_first('/ns:Document/ns:BkToCstmrDbtCdtNtfctn/ns:GrpHdr/ns:CreDtTm/text()', 
                   xml_payload,
                   ARRAY[array['ns', 'urn:iso:std:iso:20022:tech:xsd:camt.054.001.08']]) AS cre_dt_tm
FROM wire.stages;

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