PostgreSQL嵌套XML数据提取及表插入问题，寻求非XPath解决方案

你的问题根源在于把pat的id和嵌套的pat_map数据分开查询，没有建立关联，导致两个结果集做了笛卡尔积。下面给你两种可行的解决方案：

方案一：修复XPath关联逻辑 #

我们需要先把每个<pat>节点作为一个整体处理，同时提取该节点下的id和对应的所有<pat_map>，再展开嵌套的pat_map数据：

WITH xml_data AS (
  SELECT '<?xml version="1.0" encoding="UTF-8"?> 
<root> 
<pats> 
<pat> 
<id>1</id> 
<pat_maps> 
<pat_map> 
<pgid>100</pgid> 
<pgname>test</pgname> 
</pat_map> 
<pat_map> 
<pgid>101</pgid> 
<pgname>test1</pgname> 
</pat_map> 
</pat_maps> 
</pat> 
<pat> 
<id>2</id> 
<pat_maps> 
<pat_map> 
<pgid>102</pgid> 
<pgname>test2</pgname> 
</pat_map> 
</pat_maps> 
</pat> 
<pat> 
<id>3</id> 
<pat_maps> 
<pat_map> 
<pgid>104</pgid> 
<pgname>test6</pgname> 
</pat_map> 
<pat_map> 
<pgid>105</pgid> 
<pgname>test7</pgname> 
</pat_map> 
</pat_maps> 
</pat> 
</pats> 
</root>'::xml AS xml_content
),
pat_nodes AS (
  SELECT unnest(xpath('/root/pats/pat', xml_content)) AS pat_node
  FROM xml_data
)
SELECT
  (xpath('id/text()', pat_node))[1]::text AS id,
  unnest(xpath('pat_maps/pat_map/pgid/text()', pat_node))::text AS pgid,
  unnest(xpath('pat_maps/pat_map/pgname/text()', pat_node))::text AS pgname
FROM pat_nodes;

逻辑说明： #

1. 先把所有<pat>节点提取出来作为单独的行（pat_nodes CTE）
2. 对每个pat节点，同时提取它的id，以及该节点下所有的pgid和pgname
3. 用unnest展开pgid和pgname时，PostgreSQL会自动按位置关联同一pat下的对应数据，不会出现笛卡尔积

方案二：使用XMLTABLE（更简洁的非XPath直接拆分方式） #

PostgreSQL支持XMLTABLE函数，这是SQL标准中处理XML转关系表的方法，写法更直观，不需要手动嵌套unnest和xpath：

WITH xml_data AS (
  SELECT '<?xml version="1.0" encoding="UTF-8"?> 
<root> 
<pats> 
<pat> 
<id>1</id> 
<pat_maps> 
<pat_map> 
<pgid>100</pgid> 
<pgname>test</pgname> 
</pat_map> 
<pat_map> 
<pgid>101</pgid> 
<pgname>test1</pgname> 
</pat_map> 
</pat_maps> 
</pat> 
<pat> 
<id>2</id> 
<pat_maps> 
<pat_map> 
<pgid>102</pgid> 
<pgname>test2</pgname> 
</pat_map> 
</pat_maps> 
</pat> 
<pat> 
<id>3</id> 
<pat_maps> 
<pat_map> 
<pgid>104</pgid> 
<pgname>test6</pgname> 
</pat_map> 
<pat_map> 
<pgid>105</pgid> 
<pgname>test7</pgname> 
</pat_map> 
</pat_maps> 
</pat> 
</pats> 
</root>'::xml AS xml_content
)
SELECT x.id, y.pgid, y.pgname
FROM xml_data,
     XMLTABLE('/root/pats/pat'
              PASSING xml_content
              COLUMNS id INT PATH 'id',
                      pat_maps XML PATH 'pat_maps') AS x,
     XMLTABLE('/pat_maps/pat_map'
              PASSING x.pat_maps
              COLUMNS pgid INT PATH 'pgid',
                      pgname TEXT PATH 'pgname') AS y;

逻辑说明： #

1. 第一层XMLTABLE拆分每个<pat>节点，提取id和对应的<pat_maps>XML片段
2. 第二层XMLTABLE基于每个pat的pat_maps片段，拆分出对应的pgid和pgname
3. 这种方式天然保持了id和嵌套数据的关联关系，完全避免笛卡尔积问题

两种方案都能得到你期望的结果：

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