如何修改PostgreSQL查询以筛选符合XPath条件的行？

我先梳理下你的场景：

你有这样的XML数据：

<Transaction > 
  <UUID>2017-03-17T08:00:00-086F0ADD43</UUID> 
  <SequenceNumber Type="a">1</SequenceNumber> 
  <SequenceNumber Type="b">1</SequenceNumber> 
</Transaction> 
<Transaction > 
  <UUID>2017-03-17T08:00:00-086F0ADD43</UUID> 
  <SequenceNumber Type="a">2</SequenceNumber> 
  <SequenceNumber Type="b">2</SequenceNumber> 
</Transaction>

当前使用的查询语句是：

select xmldata, cast ((xpath('/Transaction/SequenceNumber[@Type="b" and text()="1"]/text()', xmldata)) AS TEXT) from tbltransaction

这个查询会返回表中所有行，结果如下：

xmldata | xpath
---------------+-----
 <Transaction> | {1}
 <Transaction> | {}

但你希望只保留XPath结果为{1}的行，也就是只返回第一行数据。

解决方案 #

你需要在查询中添加WHERE子句来过滤不符合条件的行，这里有两种可行的修改方式：

方式一：直接对比XPath返回的数组

select 
  xmldata, 
  cast((xpath('/Transaction/SequenceNumber[@Type="b" and text()="1"]/text()', xmldata)) AS TEXT) as xpath
from tbltransaction
where xpath('/Transaction/SequenceNumber[@Type="b" and text()="1"]/text()', xmldata) = ARRAY['1']::text[]

方式二：通过展开数组检查元素存在性

select 
  xmldata, 
  cast((xpath('/Transaction/SequenceNumber[@Type="b" and text()="1"]/text()', xmldata)) AS TEXT) as xpath
from tbltransaction
where exists (
  select 1 
  from unnest(xpath('/Transaction/SequenceNumber[@Type="b" and text()="1"]/text()', xmldata)) val
  where val = '1'
)

说明 #

• 方式一利用了PostgreSQL中XPath函数返回数组的特性：当匹配到目标节点时，返回包含'1'的数组；未匹配到则返回空数组，直接对比数组即可过滤。
• 方式二通过unnest将数组展开为行，再检查是否存在值为'1'的元素，逻辑更直观，也适用于更复杂的匹配场景。

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

xmldata | xpath
---------------+-----
 <Transaction> | {1}

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