PostgreSQL解析含缺失键的XML并生成结构化查询结果

针对你描述的XML格式（每行是连续的key/value节点对，key按固定顺序排列但可能缺失），可以用PostgreSQL内置的xpath()函数提取对应值，生成结构化的列数据。

完整可运行SQL #

with 
sample as
(
    select '<key>foo</key><string>foo_value_1</string><key>bar</key><string>bar_value_1</string><key>baz</key><string>baz_value_1</string>'::xml as xmlcol
    union all
    select '<key>foo</key><string>foo_value_2</string><key>baz</key><string>baz_value_2</string>'::xml as xmlcol
)
select 
    -- 提取foo对应的value
    (xpath('//key[text()="foo"]/following-sibling::string[1]/text()', xmlcol))[1]::text AS foo,
    -- 提取bar对应的value，不存在则返回NULL
    (xpath('//key[text()="bar"]/following-sibling::string[1]/text()', xmlcol))[1]::text AS bar,
    -- 提取baz对应的value
    (xpath('//key[text()="baz"]/following-sibling::string[1]/text()', xmlcol))[1]::text AS baz
from sample;

代码说明 #

1. XPath表达式逻辑：
   • //key[text()="foo"]：定位XML中内容为foo的<key>节点
   • /following-sibling::string[1]：取该<key>节点紧邻的下一个<string>兄弟节点（因为key和value是成对出现的）
   • /text()：提取<string>节点的文本内容
2. 结果处理：
   • xpath()函数返回匹配结果的数组，[1]取数组第一个元素（每个key只会出现一次）
   • ::text将XML类型的结果转换为普通文本
   • 当某个key不存在时，xpath()返回空数组，取[1]会得到NULL，正好适配缺失场景

运行上述SQL后，就能得到你需要的结构化结果：

+-------------+-------------+-------------+
|     foo     |     bar     |     baz     |
+-------------+-------------+-------------+
| foo_value_1 | bar_value_1 | baz_value_1 |
| foo_value_2 | NULL        | baz_value_2 |
+-------------+-------------+-------------+

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