如何用XPath结合PostgreSQL XML2扩展提取XML层级id组合？

需求说明 #

现有XML文档结构如下：

<A id="first">
  <B id="second">
    <C id="third"/>
    <C id="fourth"/>
  </B>
  <B id="fifth">
    <C id="sixth"/>
  </B>
  <B id="seventh">
    ...
  </B>
</A>

需要提取保持层级关系的id属性组合，输出格式示例：

first,second,third
first,second,fourth
first,fifth,sixth
...

最终通过PostgreSQL的xml2扩展查询数据库中的XML字段，将结果拆分为多行表格，目标结构如下：

解决方案 #

1. 启用xml2扩展 #

如果尚未安装该扩展，先执行以下语句：

CREATE EXTENSION IF NOT EXISTS xml2;

2. 直接生成目标表格（推荐） #

无需先生成逗号分隔字符串，直接输出分栏的表格结果：

仅返回包含C节点的行

SELECT
  (xpath('string(ancestor::A/@id)', c_node))[1]::text AS "A",
  (xpath('string(parent::B/@id)', c_node))[1]::text AS "B",
  (xpath('string(@id)', c_node))[1]::text AS "C"
FROM
  your_table,
  unnest(xpath('/A/B/C', your_xml_column)) AS c_node;

逻辑：直接定位所有<C>节点，通过XPath的ancestor和parent轴向上获取对应<A>、<B>节点的id属性，再拆分为单行记录。

返回所有B节点（含无C节点的行）

如果需要包含没有<C>子节点的<B>行（此时C字段为NULL），使用左关联查询：

SELECT
  (xpath('string(ancestor::A/@id)', b_node))[1]::text AS "A",
  (xpath('string(@id)', b_node))[1]::text AS "B",
  COALESCE((xpath('string(@id)', c_node))[1]::text, NULL) AS "C"
FROM
  your_table,
  unnest(xpath('/A/B', your_xml_column)) AS b_node
LEFT JOIN LATERAL
  unnest(xpath('C', b_node)) AS c_node ON true;

3. 先生成逗号分隔字符串再拆分 #

如果要先得到逗号分隔的组合字符串，再拆分为行，可利用xml2的xpath_list函数：

SELECT
  unnest(string_to_array(
    xpath_list(your_xml_column, '/A/B/C/concat(ancestor::A/@id, ",", parent::B/@id, ",", @id)', ','),
    ','
  )) AS id_combinations
FROM your_table;

逻辑：通过XPath的concat函数将三级id拼接为逗号分隔的字符串，xpath_list收集所有结果并按指定分隔符连接，最后用string_to_array和unnest拆分为单行记录。

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