PostgreSQL JSON列提取XML内FName与TIIN值报错求助

错误原因分析 #

你遇到的ERROR: cannot cast type record to xml是因为命名空间参数格式错误：你试图把(前缀, 命名空间URI)的记录直接转为XML，但PostgreSQL的xpath()函数第三个参数需要的是text[][]类型的二维数组，而非XML数组。另外你的XPath路径存在拼写错误：XML里的标签是pvs:ValidateTinName，但你写的是ValidateTiinName（多了一个i）。

修正后的查询语句 #

提取单个字段（如FName） #

select  
  xpath(
    '/soap:Envelope/soap:Body/pvs:ValidateTinName/pvs:TinName/pvs:FName/text()',
    (a.json_column::json->>'body')::xml,
    array[
      array['soap', 'http://www.a.org/05/soap-envelope'],
      array['pvs', 'http://www.z.com/WebServices/PMZService/']
    ]
  )::text[] AS fname_value
from table a;

同时提取TIIN和FName #

如果需要一次性获取两个字段，可以通过子查询复用命名空间参数，简化语句：

select
  (xpath('/soap:Envelope/soap:Body/pvs:ValidateTinName/pvs:TinName/pvs:TIIN/text()', body_xml, ns_array))[1]::text as tiin,
  (xpath('/soap:Envelope/soap:Body/pvs:ValidateTinName/pvs:TinName/pvs:FName/text()', body_xml, ns_array))[1]::text as fname
from (
  select
    (json_column::json->>'body')::xml as body_xml,
    array[
      array['soap', 'http://www.a.org/05/soap-envelope'],
      array['pvs', 'http://www.z.com/WebServices/PMZService/']
    ] as ns_array
  from table a
) t;

关键修正点 #

• 命名空间参数改为text[][]二维数组，每个子数组包含前缀和对应的URI
• 修正XPath路径中的标签拼写错误（ValidateTiinName → ValidateTinName）
• 用[1]直接取xpath返回数组的第一个元素（每个标签仅返回一个值），避免返回数组格式结果
• 确保XML命名空间URI完全匹配：示例XML中pvs的命名空间是http://www.z.com/WebServices/PMZService/（带结尾斜杠），需与参数里的保持一致

内容的提问来源于stack exchange，提问作者Luis Enrique Garduno Morales