PostgreSQL 13中如何无需使用xmltable从XML列提取订单明细数据

核心实现完全依托PG13原生内置的xpath、unnest函数能力，不需要引入xmltable，可直接在Windows环境的pgAdmin中执行

可直接运行的完整查询 #

SELECT
  -- 直接取原表已有的订单ID，无需新生成行号
  o.id,
  trim((xpath('/item/name/text()', item_node))[1]::text) AS "Name",
  trim((xpath('/item/quantity/text()', item_node))[1]::text) AS "Quantity",
  trim((xpath('/item/price/text()', item_node))[1]::text) AS "Price"
FROM orders o,
-- 核心拆分逻辑：炸开所有item节点为独立行
unnest(xpath('/order/items/item', o.xml::xml)) AS item_node;

逻辑拆解 #

• 嵌套节点拆分环节：
  • xpath('/order/items/item', o.xml::xml) 按照XPath规则扫描每行订单的XML内容，定位到所有属于<items>父节点下的<item>商品节点，返回XML类型的节点数组
  • unnest(节点数组) 把数组内的每一个<item>节点拆成单独的结果行，实现多行拆分的效果，全程不调用xmltable
• 单节点值提取环节：
  • 拆分后的每一行item_node就是一个完整的<item>XML片段，以此为上下文用xpath提取内部的name、quantity、price子节点文本值
  • [1]::text是因为xpath匹配结果固定返回数组格式，单值场景下取数组第一个元素并转成文本类型
  • 搭配trim()处理XML节点前后的换行、空格，和示例期望的输出格式对齐
• 关于之前的行号逻辑误区：

  你之前尝试用row_number() over () as id生成ID的写法不符合需求：拆分后的订单ID是原表已经存储的业务主键，同一个订单下的多商品行共用同一个订单ID，不需要用全局行号生成伪ID，和示例期望的输出完全匹配。

适配说明 #

• 若你的xml列已经是PG原生的xml类型，可去掉代码里的::xml强转，逻辑完全兼容
• 若需要对提取的数值做后续计算，可直接把quantity、price字段的返回值强转为对应数值类型，比如将price部分写成trim((xpath('/item/price/text()', item_node))[1]::text)::numeric
• 所有用到的函数均为PG13原生内置能力，不需要安装额外扩展，适配Windows环境下的pgAdmin操作场景

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