PostgreSQL实战指南：多领域数据库应用与实践

第三部分：PostgreSQL与NoSQL特性

PostgreSQL 凭借其对 JSON/JSONB、XML、Hstore 等数据类型的原生支持以及强大的全文搜索能力，完美融合了关系型数据库的严谨性与 NoSQL 的灵活性。本部分将详细介绍如何利用这些特性来高效处理半结构化和非结构化数据，满足现代应用的多样化需求。

第13章：XML数据处理与应用

尽管 JSON 在现代 Web API 中已成为主流，但 XML（eXtensible Markup Language）在许多企业级应用、配置文件和行业标准（如金融、医疗）中仍然扮演着重要角色。PostgreSQL 提供了强大的 XML 数据类型和一系列函数，用于在数据库中存储、查询和转换 XML 数据。

13.1 XML 数据类型与验证

PostgreSQL 的 XML 数据类型用于存储 XML 文档。与 JSON 类型类似，它会检查输入的值是否是格式良好的 XML。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    content XML
);

-- 插入格式良好的 XML
INSERT INTO articles (content) VALUES
('<article><title>The Power of SQL</title><author>John Doe</author></article>');

-- 插入格式错误的 XML (将会失败)
-- INSERT INTO articles (content) VALUES ('<article><title>Incomplete');

XML 验证:

XML 类型可以根据 XML Schema 进行验证，以确保其内容和结构符合预定义的规则。

1
2
3
4
5

-- 假设我们有一个 XML Schema 定义
-- CREATE XML SCHEMA COLLECTION my_schema AS '...';

-- 创建表时指定 Schema
-- CREATE TABLE articles_validated (id SERIAL PRIMARY KEY, content XML(DOCUMENT(my_schema)));
13.2 使用 XPath 查询 XML 数据

XPath (XML Path Language) 是一种用于在 XML 文档中查找信息的语言。PostgreSQL 提供了 xpath() 函数来执行 XPath 查询。

  • xpath(xpath_expression, xml_document): 返回一个 xml[] 数组，包含所有匹配的 XML 节点。
  • xpath_exists(xpath_expression, xml_document): 检查是否存在匹配的节点，返回 boolean。

XPath 查询示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- 准备数据
TRUNCATE articles RESTART IDENTITY;
INSERT INTO articles (content) VALUES
('<article id="101">
    <title>The Power of SQL</title>
    <author>John Doe</author>
    <tags>
        <tag>sql</tag>
        <tag>database</tag>
    </tags>
</article>'),
('<article id="102">
    <title>Mastering PostgreSQL</title>
    <author>Jane Smith</author>
    <tags>
        <tag>postgresql</tag>
        <tag>database</tag>
    </tags>
</article>');

-- 提取所有文章的标题
SELECT xpath('/article/title/text()', content) FROM articles;
-- 结果: {"The Power of SQL"}, {"Mastering PostgreSQL"} (类型为 xml[])

-- 提取特定作者的文章标题
SELECT xpath('/article[author="Jane Smith"]/title/text()', content)
FROM articles;
-- 结果: {"Mastering PostgreSQL"}

-- 检查是否存在关于 'sql' 的标签
SELECT id, xpath_exists('/article/tags/tag[text()="sql"]', content)
FROM articles;
-- 结果:
-- id | xpath_exists
-- ---|--------------
--  1 | t
--  2 | f
13.3 使用 xmltable 将 XML 转换为关系表

xmltable 是一个非常强大的函数，它可以将复杂的 XML 文档“展开”成一个关系型表格，使得后续的查询和分析变得异常简单。

xmltable 语法:

1
2
3
4
5
6
7

xmltable(
    xml_namespaces,
    row_xpath,
    COLUMNS
        column_name type PATH column_xpath DEFAULT default_value,
        ...
)

xmltable 示例:

让我们将 articles 表中的 XML 数据转换为一个结构化的表格。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT xt.*
FROM articles,
     xmltable('/article' PASSING content COLUMNS
         id INT PATH '@id',
         title TEXT PATH 'title',
         author TEXT PATH 'author'
     ) AS xt;

-- 结果:
-- id  | title                  | author
-- ----|------------------------|------------
-- 101 | The Power of SQL       | John Doe
-- 102 | Mastering PostgreSQL   | Jane Smith

处理嵌套和重复的元素:

xmltable 也可以与其他函数结合，处理更复杂的结构，比如提取所有的标签。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT
    xt.id,
    xt.title,
    tags_xt.tag
FROM
    articles,
    XMLTABLE('/article' PASSING content COLUMNS
        id INT PATH '@id',
        title TEXT PATH 'title',
        tags XML PATH 'tags'
    ) AS xt,
    XMLTABLE('/tags/tag' PASSING xt.tags COLUMNS
        tag TEXT PATH '.'
    ) AS tags_xt;

-- 结果:
-- id  | title                  | tag
-- ----|------------------------|------------
-- 101 | The Power of SQL       | sql
-- 101 | The Power of SQL       | database
-- 102 | Mastering PostgreSQL   | postgresql
-- 102 | Mastering PostgreSQL   | database
13.4 场景实战：处理 SOAP Web Service 响应

业务场景描述:

假设我们的系统需要与一个老旧的企业级 Web Service 集成，该服务使用 SOAP 协议并返回 XML 格式的响应。我们需要从响应中提取用户数据并存入关系表中。

SOAP 响应示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <ns2:getUserResponse xmlns:ns2="http://example.com/users">
      <user id="U123">
        <name>Alice</name>
        <email>[email protected]</email>
        <roles>
          <role>admin</role>
          <role>editor</role>
        </roles>
      </user>
    </ns2:getUserResponse>
  </soap:Body>
</soap:Envelope>

数据提取与存储:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- 假设 XML 响应存储在一个变量或临时表中
WITH soap_response(data) AS (
    VALUES ('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
              <soap:Body>
                <ns2:getUserResponse xmlns:ns2="http://example.com/users">
                  <user id="U123">
                    <name>Alice</name>
                    <email>[email protected]</email>
                    <roles>
                      <role>admin</role>
                      <role>editor</role>
                    </roles>
                  </user>
                </ns2:getUserResponse>
              </soap:Body>
            </soap:Envelope>'::xml)
)
SELECT
    xt.id,
    xt.name,
    xt.email,
    roles_xt.role
FROM
    soap_response,
    XMLTABLE(
        XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soap, 'http://example.com/users' AS ns2),
        '/soap:Envelope/soap:Body/ns2:getUserResponse/user'
        PASSING data
        COLUMNS
            id TEXT PATH '@id',
            name TEXT PATH 'name',
            email TEXT PATH 'email',
            roles XML PATH 'roles'
    ) AS xt,
    XMLTABLE('/roles/role' PASSING xt.roles COLUMNS
        role TEXT PATH '.'
    ) AS roles_xt;

代码解释与思考:

  • XMLNAMESPACES: 当 XML 文档包含命名空间（namespace）时，必须在 xmltable 中使用 XMLNAMESPACES 子句来声明它们，并为每个命名空间分配一个前缀。这样，在 XPath 表达式中就可以使用这些前缀来正确地选择节点（如 soap:Body, ns2:getUserResponse）。
  • PASSING: 这个关键字用于将源 XML 文档传递给 xmltable 函数进行处理。
  • PATH: 在 COLUMNS 定义中，PATH 关键字后面跟着一个 XPath 表达式，它指定了如何从 row_xpath 匹配到的每个 XML 节点中提取列数据。@id 用于提取属性值，title 用于提取子元素的值，. 用于提取当前节点自身的文本内容。
  • 分层解析: 通过将第一个 xmltable 的输出（xt.roles）作为第二个 xmltable 的输入，我们实现了一种分层、逐步的解析策略，优雅地处理了嵌套的 XML 结构。
13.5 总结

本章我们学习了如何在 PostgreSQL 中使用 XML 数据类型来处理半结构化数据。我们掌握了 xpath() 函数的基本用法，并深入实践了功能强大的 xmltable 函数，它可以将复杂的 XML 文档高效地转换为关系型数据，极大地简化了后续的数据处理和分析。

在下一章，我们将探讨 PostgreSQL 的另一种键值对存储方案——Hstore。