PostgreSQL XML 函数详解

PostgreSQL 提供了丰富的 XML 处理函数，使数据库能够高效地处理、生成和操作 XML 数据。本章将深入探讨这些函数的使用方法和实际应用场景。

概述

INFO

前置条件使用大多数 XML 函数需要在编译 PostgreSQL 时使用 configure --with-libxml 选项。XML 类型的详细信息请参阅相关文档章节。

XML 函数主要分为四大类：

生成 XML 内容：从 SQL 数据创建 XML
XML 谓词：验证和检查 XML 数据
处理 XML：查询和操作 XML 内容
表映射到 XML：将关系型数据转换为 XML 格式

生成 XML 内容

xmltext - 创建文本节点

xmltext 函数将文本字符串转换为 XML 文本节点，自动转义特殊字符。

语法：

sql

xmltext(text) → xml

实际应用场景：在电商系统中处理用户评论，确保特殊字符被正确转义。

基础示例业务场景

sql

-- 处理包含特殊字符的文本
SELECT xmltext('商品评价：很好！价格 < 100元，质量 > 预期');

-- 输出结果
-- 商品评价：很好！价格 &lt; 100元，质量 &gt; 预期

sql

-- 电商评论系统：安全处理用户输入
CREATE TABLE product_reviews (
    id SERIAL PRIMARY KEY,
    product_name TEXT,
    review_text TEXT,
    review_xml XML
);

-- 插入带特殊字符的评论
INSERT INTO product_reviews (product_name, review_text, review_xml)
VALUES
    ('iPhone 15', '性能很棒！比iPhone 14 > 20%提升',
     xmltext('性能很棒！比iPhone 14 > 20%提升'));

-- 查询结果
SELECT product_name, review_xml FROM product_reviews;

分析过程：

xmltext 自动将 <、>、& 等特殊字符转义
确保生成的 XML 文档格式良好
防止 XML 注入攻击

xmlcomment - 创建注释节点

创建 XML 注释，适用于在 XML 文档中添加元数据或说明信息。

语法：

sql

xmlcomment(text) → xml

基础用法报表系统应用

sql

SELECT xmlcomment('数据生成时间：2024-01-15');
-- 输出：<!--数据生成时间：2024-01-15-->

sql

-- 财务报表系统：在 XML 报表中添加生成信息
CREATE OR REPLACE FUNCTION generate_financial_report(report_date DATE)
RETURNS XML AS $$
BEGIN
    RETURN xmlcomment('财务报表生成于 ' || NOW()::TEXT ||
                     ' 报表日期：' || report_date::TEXT);
END;
$$ LANGUAGE plpgsql;

-- 使用示例
SELECT generate_financial_report('2024-01-31');

WARNING

注意事项注释内容不能包含 -- 字符串或以 - 结尾，否则会生成无效的 XML 注释。

xmlconcat - 连接 XML 片段

连接多个 XML 值，创建复合的 XML 内容。

语法：

sql

xmlconcat(xml [, ...]) → xml

业务场景：构建复杂的产品目录 XML。

简单连接产品目录系统

sql

SELECT xmlconcat('<product>iPhone</product>', '<price>999</price>');
-- 输出：<product>iPhone</product><price>999</price>

sql

-- 电商产品目录：动态构建产品 XML
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price DECIMAL(10,2),
    category TEXT,
    description TEXT
);

-- 插入测试数据
INSERT INTO products VALUES
    (1, 'iPhone 15', 999.99, '手机', '最新款苹果手机'),
    (2, 'MacBook Pro', 1999.99, '笔记本', '专业级笔记本电脑');

-- 构建产品 XML 目录
SELECT xmlconcat(
    xmlcomment('产品目录生成时间：' || NOW()),
    xmlelement(name products,
        xmlagg(
            xmlelement(name product,
                xmlelement(name id, id),
                xmlelement(name name, name),
                xmlelement(name price, price),
                xmlelement(name category, category)
            )
        )
    )
) AS product_catalog
FROM products;

xmlelement - 创建 XML 元素

最灵活的 XML 生成函数，可以创建带属性和内容的 XML 元素。

语法：

sql

xmlelement(NAME element_name
          [, XMLATTRIBUTES(attr_value [AS attr_name] [, ...])]
          [, content [, ...]])

基础元素订单管理系统

sql

-- 创建简单元素
SELECT xmlelement(name customer, '张三');
-- 输出：<customer>张三</customer>

-- 创建带属性的元素
SELECT xmlelement(name customer,
                  xmlattributes('001' as id, 'VIP' as level),
                  '张三');
-- 输出：<customer id="001" level="VIP">张三</customer>

sql

-- 订单管理系统：生成订单 XML
CREATE TABLE orders (
    order_id TEXT PRIMARY KEY,
    customer_name TEXT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    status TEXT
);

CREATE TABLE order_items (
    order_id TEXT,
    product_name TEXT,
    quantity INTEGER,
    unit_price DECIMAL(10,2)
);

-- 插入测试数据
INSERT INTO orders VALUES
    ('ORD001', '张三', '2024-01-15', 1299.98, '已发货');

INSERT INTO order_items VALUES
    ('ORD001', 'iPhone 15', 1, 999.99),
    ('ORD001', '手机壳', 1, 299.99);

-- 生成完整订单 XML
SELECT xmlelement(name order,
    xmlattributes(o.order_id as id, o.status as status),
    xmlelement(name customer, o.customer_name),
    xmlelement(name order_date, o.order_date),
    xmlelement(name items,
        (SELECT xmlagg(
            xmlelement(name item,
                xmlattributes(oi.quantity as qty),
                xmlelement(name product, oi.product_name),
                xmlelement(name price, oi.unit_price)
            )
        ) FROM order_items oi WHERE oi.order_id = o.order_id)
    ),
    xmlelement(name total, o.total_amount)
) AS order_xml
FROM orders o
WHERE o.order_id = 'ORD001';

复杂示例输出：

xml

<order id="ORD001" status="已发货">
  <customer>张三</customer>
  <order_date>2024-01-15</order_date>
  <items>
    <item qty="1">
      <product>iPhone 15</product>
      <price>999.99</price>
    </item>
    <item qty="1">
      <product>手机壳</product>
      <price>299.99</price>
    </item>
  </items>
  <total>1299.98</total>
</order>

xmlforest - 批量创建元素

将多个表达式转换为同名的 XML 元素，适用于快速创建多个平级元素。

语法：

sql

xmlforest(content [AS element_name] [, ...])

基础用法员工信息系统

sql

-- 快速创建多个元素
SELECT xmlforest('张三' as name, 25 as age, '工程师' as job);
-- 输出：<name>张三</name><age>25</age><job>工程师</job>

sql

-- HR 系统：批量导出员工信息
CREATE TABLE employees (
    emp_id TEXT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary DECIMAL(10,2),
    hire_date DATE
);

-- 插入测试数据
INSERT INTO employees VALUES
    ('E001', '张', '三', '技术部', 8000.00, '2023-01-15'),
    ('E002', '李', '四', '销售部', 6000.00, '2023-03-20');

-- 生成员工信息 XML
SELECT xmlelement(name employee,
    xmlattributes(emp_id as id),
    xmlforest(
        first_name || last_name as full_name,
        department,
        salary,
        hire_date
    )
) AS employee_xml
FROM employees;

-- 输出示例：
-- <employee id="E001">
--   <full_name>张三</full_name>
--   <department>技术部</department>
--   <salary>8000.00</salary>
--   <hire_date>2023-01-15</hire_date>
-- </employee>

xmlagg - 聚合 XML 数据

将多行 XML 数据聚合为单个 XML 值，常用于创建列表或集合。

语法：

sql

xmlagg(xml_expression [ORDER BY sort_expression])

基础聚合销售报表系统

sql

-- 聚合多个产品信息
WITH products AS (
    SELECT unnest(ARRAY['iPhone', 'iPad', 'MacBook']) as name,
           unnest(ARRAY[999, 599, 1299]) as price
)
SELECT xmlagg(xmlelement(name product, name)) as product_list
FROM products;
-- 输出：<product>iPhone</product><product>iPad</product><product>MacBook</product>

sql

-- 销售报表：按部门聚合销售数据
CREATE TABLE sales_data (
    department TEXT,
    month TEXT,
    sales_amount DECIMAL(10,2)
);

INSERT INTO sales_data VALUES
    ('技术部', '2024-01', 50000),
    ('技术部', '2024-02', 55000),
    ('销售部', '2024-01', 80000),
    ('销售部', '2024-02', 75000);

-- 生成按部门分组的销售报表
SELECT department,
       xmlelement(name monthly_sales,
           xmlagg(
               xmlelement(name month_data,
                   xmlattributes(month as month),
                   sales_amount
               ) ORDER BY month
           )
       ) as sales_xml
FROM sales_data
GROUP BY department;

-- 输出示例：
-- 技术部: <monthly_sales>
--          <month_data month="2024-01">50000</month_data>
--          <month_data month="2024-02">55000</month_data>
--        </monthly_sales>

XML 谓词和验证

xmlexists - 检查 XPath 表达式

验证 XML 文档中是否存在满足 XPath 表达式的节点。

语法：

sql

xmlexists(xpath_expression PASSING xml_document)

基础验证配置管理系统

sql

-- 检查 XML 中是否存在特定元素
SELECT xmlexists('/product/price' PASSING '<product><name>iPhone</name><price>999</price></product>');
-- 输出：true

sql

-- 系统配置验证：确保必要的配置项存在
CREATE TABLE system_configs (
    config_name TEXT PRIMARY KEY,
    config_xml XML
);

INSERT INTO system_configs VALUES
    ('database', '<config><host>localhost</host><port>5432</port><database>mydb</database></config>'),
    ('cache', '<config><host>redis-server</host><timeout>30</timeout></config>');

-- 验证配置完整性
SELECT config_name,
       xmlexists('/config/host' PASSING config_xml) as has_host,
       xmlexists('/config/port' PASSING config_xml) as has_port,
       xmlexists('/config/database' PASSING config_xml) as has_database
FROM system_configs;

-- 输出：
-- database | true  | true  | true
-- cache    | true  | false | false

xml_is_well_formed - 验证 XML 格式

检查文本字符串是否为格式良好的 XML。

格式验证数据导入验证

sql

-- 验证不同格式的 XML
SELECT
    xml_is_well_formed('<product><name>iPhone</name></product>') as valid_xml,
    xml_is_well_formed('<product><name>iPhone</product>') as invalid_xml,
    xml_is_well_formed('plain text') as plain_text;

-- 输出：true | false | false (当 xmloption 设置为 CONTENT 时为 true)

sql

-- 数据导入系统：验证 XML 数据有效性
CREATE TABLE xml_import_log (
    id SERIAL PRIMARY KEY,
    source_data TEXT,
    is_valid BOOLEAN,
    import_date TIMESTAMP DEFAULT NOW()
);

-- 批量验证导入数据
WITH import_data AS (
    SELECT unnest(ARRAY[
        '<order><id>1</id><customer>张三</customer></order>',
        '<order><id>2</id><customer>李四</order>',  -- 缺少结束标签
        '<order id="3"><customer>王五</customer></order>'
    ]) as xml_text
)
INSERT INTO xml_import_log (source_data, is_valid)
SELECT xml_text, xml_is_well_formed(xml_text)
FROM import_data;

-- 查看验证结果
SELECT source_data, is_valid FROM xml_import_log;

XML 数据处理

xpath - XPath 查询

使用 XPath 表达式从 XML 文档中提取数据。

语法：

sql

xpath(xpath_expression, xml_document [, namespace_array])

基础查询客户管理系统

sql

-- 提取 XML 中的特定数据
SELECT xpath('/product/name/text()',
              '<product><name>iPhone 15</name><price>999</price></product>');
-- 输出：{iPhone 15}

SELECT xpath('/product/price/text()',
              '<product><name>iPhone 15</name><price>999</price></product>');
-- 输出：{999}

sql

-- CRM 系统：解析客户信息 XML
CREATE TABLE customer_profiles (
    customer_id TEXT PRIMARY KEY,
    profile_xml XML
);

INSERT INTO customer_profiles VALUES
    ('C001', '<profile>
                <personal>
                    <name>张三</name>
                    <age>28</age>
                    <city>北京</city>
                </personal>
                <preferences>
                    <category>电子产品</category>
                    <category>图书</category>
                </preferences>
                <contact>
                    <email>[email protected]</email>
                    <phone>13800138000</phone>
                </contact>
              </profile>');

-- 提取客户基本信息
SELECT customer_id,
       xpath('/profile/personal/name/text()', profile_xml)[1]::TEXT as name,
       xpath('/profile/personal/age/text()', profile_xml)[1]::TEXT::INTEGER as age,
       xpath('/profile/personal/city/text()', profile_xml)[1]::TEXT as city,
       xpath('/profile/contact/email/text()', profile_xml)[1]::TEXT as email
FROM customer_profiles;

-- 提取客户偏好列表
SELECT customer_id,
       unnest(xpath('/profile/preferences/category/text()', profile_xml))::TEXT as preference
FROM customer_profiles;

xmltable - 表格化 XML 数据

将 XML 数据转换为关系型表格格式，是处理复杂 XML 数据的强大工具。

语法：

sql

XMLTABLE(
    [XMLNAMESPACES(namespace_definitions),]
    row_expression PASSING xml_document
    COLUMNS column_definitions
)

基础表格化库存管理系统

sql

-- 将订单 XML 转换为表格
WITH order_xml AS (
    SELECT '<orders>
              <order id="1" date="2024-01-15">
                <customer>张三</customer>
                <amount>999.99</amount>
              </order>
              <order id="2" date="2024-01-16">
                <customer>李四</customer>
                <amount>1299.50</amount>
              </order>
            </orders>'::xml as data
)
SELECT *
FROM order_xml,
     XMLTABLE('/orders/order' PASSING data
              COLUMNS order_id INTEGER PATH '@id',
                      order_date DATE PATH '@date',
                      customer_name TEXT PATH 'customer',
                      amount DECIMAL(10,2) PATH 'amount');

sql

-- 库存系统：解析复杂的库存 XML 数据
CREATE TABLE inventory_imports (
    import_id SERIAL PRIMARY KEY,
    import_data XML,
    import_date TIMESTAMP DEFAULT NOW()
);

-- 插入复杂的库存 XML 数据
INSERT INTO inventory_imports (import_data) VALUES (
    '<inventory_update>
       <warehouse id="WH001" name="北京仓库">
         <items>
           <item sku="SKU001" category="electronics">
             <name>iPhone 15</name>
             <quantity>100</quantity>
             <unit_cost>800.00</unit_cost>
             <supplier>苹果公司</supplier>
           </item>
           <item sku="SKU002" category="electronics">
             <name>iPad Pro</name>
             <quantity>50</quantity>
             <unit_cost>1200.00</unit_cost>
             <supplier>苹果公司</supplier>
           </item>
         </items>
       </warehouse>
       <warehouse id="WH002" name="上海仓库">
         <items>
           <item sku="SKU003" category="books">
             <name>PostgreSQL 指南</name>
             <quantity>200</quantity>
             <unit_cost>89.00</unit_cost>
             <supplier>技术出版社</supplier>
           </item>
         </items>
       </warehouse>
     </inventory_update>'
);

-- 使用 XMLTABLE 解析库存数据
SELECT warehouse_id, warehouse_name, sku, product_name,
       category, quantity, unit_cost, supplier,
       (quantity * unit_cost) as total_value
FROM inventory_imports ii,
     XMLTABLE('/inventory_update/warehouse' PASSING ii.import_data
              COLUMNS warehouse_id TEXT PATH '@id',
                      warehouse_name TEXT PATH '@name',
                      items XML PATH 'items') as wh,
     XMLTABLE('/items/item' PASSING wh.items
              COLUMNS sku TEXT PATH '@sku',
                      category TEXT PATH '@category',
                      product_name TEXT PATH 'name',
                      quantity INTEGER PATH 'quantity',
                      unit_cost DECIMAL(10,2) PATH 'unit_cost',
                      supplier TEXT PATH 'supplier') as items;

输出示例： | warehouse_id | warehouse_name | sku | product_name | category | quantity | unit_cost | supplier | total_value | |-------------|---------------|--------|------------------|-------------|----------|-----------|------------|-------------| | WH001 | 北京仓库 | SKU001 | iPhone 15 | electronics | 100 | 800.00 | 苹果公司 | 80000.00 | | WH001 | 北京仓库 | SKU002 | iPad Pro | electronics | 50 | 1200.00 | 苹果公司 | 60000.00 | | WH002 | 上海仓库 | SKU003 | PostgreSQL 指南 | books | 200 | 89.00 | 技术出版社 | 17800.00 |

表到 XML 的映射

table_to_xml - 表转 XML

将整个表的数据转换为 XML 格式，适用于数据导出和接口对接。

语法：

sql

table_to_xml(table_name, nulls, tableforest, targetns)

基础转换API 接口应用

sql

-- 创建示例表
CREATE TABLE products_demo (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price DECIMAL(10,2),
    category TEXT
);

INSERT INTO products_demo VALUES
    (1, 'iPhone 15', 999.99, '手机'),
    (2, 'MacBook Pro', 1999.99, '笔记本'),
    (3, 'AirPods', 199.99, '耳机');

-- 转换为 XML（文档格式）
SELECT table_to_xml('products_demo', true, false, '');

sql

-- API 数据导出：为第三方系统提供 XML 格式数据
CREATE OR REPLACE FUNCTION export_products_xml(category_filter TEXT DEFAULT NULL)
RETURNS XML AS $$
DECLARE
    query_text TEXT;
    result_xml XML;
BEGIN
    -- 构建动态查询
    query_text := 'SELECT * FROM products_demo';

    IF category_filter IS NOT NULL THEN
        query_text := query_text || ' WHERE category = ' || quote_literal(category_filter);
    END IF;

    -- 生成 XML
    SELECT query_to_xml(query_text, true, false, 'http://company.com/products')
    INTO result_xml;

    RETURN result_xml;
END;
$$ LANGUAGE plpgsql;

-- 使用示例
SELECT export_products_xml('手机');  -- 导出手机类别
SELECT export_products_xml();       -- 导出所有产品

query_to_xml - 查询结果转 XML

将查询结果转换为 XML，提供更大的灵活性。

复杂查询转换

sql

-- 销售报表：复杂查询结果转 XML
CREATE TABLE sales_records (
    id SERIAL PRIMARY KEY,
    product_name TEXT,
    sales_date DATE,
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    sales_person TEXT
);

-- 插入测试数据
INSERT INTO sales_records VALUES
    (1, 'iPhone 15', '2024-01-15', 2, 999.99, '张三'),
    (2, 'MacBook Pro', '2024-01-15', 1, 1999.99, '张三'),
    (3, 'iPhone 15', '2024-01-16', 1, 999.99, '李四');

-- 生成销售汇总 XML 报表
SELECT query_to_xml('
    SELECT
        sales_person,
        COUNT(*) as total_orders,
        SUM(quantity) as total_quantity,
        SUM(quantity * unit_price) as total_amount,
        AVG(unit_price) as avg_unit_price
    FROM sales_records
    WHERE sales_date >= ''2024-01-15''
    GROUP BY sales_person
    ORDER BY total_amount DESC',
    true, false, 'http://company.com/sales-report'
) as sales_summary_xml;

实际应用场景总结

性能优化建议

TIP

优化技巧

索引优化：为经常查询的 XML 路径创建表达式索引

sql

CREATE INDEX idx_product_name ON orders
USING gin((xpath('/order/items/item/name/text()', order_xml)));

批量处理：使用 xmlagg 替代多次 xmlconcat 调用
内存管理：大量 XML 数据处理时考虑使用游标
缓存策略：频繁访问的 XML 查询结果可以缓存

WARNING

注意事项

XML 函数对大数据量处理时内存消耗较大
XPath 表达式的复杂度直接影响查询性能
命名空间处理需要特别注意，确保正确映射
空值处理在不同函数中行为可能不同

最佳实践

结构化设计：设计清晰的 XML 架构，避免过度嵌套
错误处理：始终验证 XML 格式和内容有效性
性能监控：监控 XML 处理函数的执行时间和资源消耗
文档规范：建立 XML 格式的文档和命名规范
版本兼容：考虑 XML 结构变更对现有代码的影响

通过合理使用 PostgreSQL 的 XML 函数，可以有效地处理各种 XML 数据需求，从简单的数据格式转换到复杂的 XML 文档生成和查询。这些函数为数据库与 XML 生态系统的集成提供了强大的支持。

PostgreSQL XML 函数详解 ​

概述 ​

生成 XML 内容 ​

xmltext - 创建文本节点 ​

xmlcomment - 创建注释节点 ​

xmlconcat - 连接 XML 片段 ​

xmlelement - 创建 XML 元素 ​

xmlforest - 批量创建元素 ​

xmlagg - 聚合 XML 数据 ​

XML 谓词和验证 ​

xmlexists - 检查 XPath 表达式 ​

xml_is_well_formed - 验证 XML 格式 ​

XML 数据处理 ​

xpath - XPath 查询 ​

xmltable - 表格化 XML 数据 ​

表到 XML 的映射 ​

table_to_xml - 表转 XML ​

query_to_xml - 查询结果转 XML ​

实际应用场景总结 ​

性能优化建议 ​

最佳实践 ​