PostgreSQL中使用xmltable()从XML提取多值映射为独立列的方法

针对带ns2命名空间的Declaration结构XML，使用xmltable()可以避免多次unnest+xpath带来的行数错位、笛卡尔积问题，一次提取所有目标节点，具体实现如下：

核心查询语句 #

SELECT
  src.id,
  src.messagebody,
  xt.functional_reference_id,
  xt.procedure_category
  -- 可按需追加其他需要提取的节点列
FROM sw_customs_message src,
XMLTABLE(
  -- 声明XML中使用的命名空间，与messagebody内的ns2前缀绑定
  XMLNAMESPACES('urn:wco:datamodel:WCO:DEC-DMS:2' AS ns2),
  -- 指定匹配的XML根节点路径
  '/ns2:Declaration'
  -- 传入待解析的XML字段
  PASSING src.messagebody
  -- 定义输出列与对应节点的相对提取路径
  COLUMNS
    functional_reference_id TEXT PATH 'ns2:FunctionalReferenceID',
    procedure_category TEXT PATH 'ns2:ProcedureCategory'
) xt;

关键注意事项 #

• 命名空间必须全路径匹配：只要XML声明了ns2命名空间，所有xpath路径中的节点（包括根节点、子节点）都必须加ns2:前缀，否则会匹配失败返回空值。
• 路径规则：COLUMNS块中写的PATH是相对于前面定义的根路径（即/ns2:Declaration）的相对路径，不需要重复书写根节点路径。
• 空值兼容：如果某行XML不存在目标节点，对应列会自动返回NULL，不会出现多节点unnest时因为返回数组长度不一致导致的行数膨胀问题。
• 重复节点处理：如果同一Declaration下存在多个同名重复节点，可将对应列类型定义为数组类型TEXT[]，即可一次性提取所有重复节点值为数组，无需额外聚合。
  例：提取多个商品编码的列定义写法为goods_codes TEXT[] PATH 'ns2:GoodsItem/ns2:CommodityCode'

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