xml - 在 PostgreSQL 中按 XML 节点值排序

Question

有没有办法使用order by使用 PostgreSQL 中 XML 列的值的功能？

例子

如果我在我的 PostgreSQL 数据库中的一个 XML 列中有这个 xml 作为一个列，有没有办法写一个 select按 <type> 对结果排序的语句属性？

<items>
 <item>
  <id>1</id>
  <name>Item #1</name>
  <type>Hardware</type>
 </item>
 <item>
  <id>2</id>
  <name>Item #2</name>
  <type>Software</type>
 </item>
</items>

谢谢。

Answer 1

基本上，您需要使用 xpath() 获取必要的 XML 元素功能 1。然后将所有结果值处理为 TEXT 类型以进一步排序(您无法将 GROUP BY 标识符应用于 Postgres XML 类型)。实际查询可能如下:

WITH extracted_data AS (
   SELECT id,unnest(xpath('//type/text()',data))::TEXT AS item_type  
   FROM xml_test
) 
SELECT id,item_type FROM extracted_data 
ORDER BY id,item_type DESC;

示例 DDL 和数据:

-- Sample DDL
CREATE TABLE xml_test (
  id SERIAL PRIMARY KEY,
  data XML
);
-- Sample Data
INSERT INTO xml_test(data) VALUES
  ('<items>
      <item>
        <id>1</id>
        <name>Item #1</name>
        <type>Hardware</type>
      </item>
      <item>
        <id>2</id>
        <name>Item #2</name>
        <type>Software</type>
      </item>
    </items>'::XML),
('<items>
      <item>
        <id>3</id>
        <name>Item #3</name>
        <type>Middleware</type>
      </item>
      <item>
        <id>4</id>
        <name>Item #4</name>
        <type>Other</type>
      </item>
    </items>'::XML);