从T-SQL迁移XML解析至PostgreSQL遇URI格式错误问题

核心问题原因 #

PostgreSQL的XML解析函数（如xpath、xmltable）默认会尝试加载schemaLocation指定的URI进行Schema验证，当URI无效或无法访问时就会抛出错误；而SQL Server的XML解析默认不强制验证Schema，所以不会触发这类问题。

解决方法：禁用XML Schema验证 #

PostgreSQL提供了xmloption设置，可通过以下方式关闭Schema验证：

1. 会话级临时禁用（推荐） #

在执行XML解析查询前，运行命令临时关闭当前会话的Schema验证，该设置仅对当前连接有效：

SET xmloption TO NOVALIDATE;

2. 全局永久设置（谨慎使用） #

若需要所有会话默认禁用验证，可修改postgresql.conf配置文件：

xmloption = novalidate

修改后需重启PostgreSQL服务生效。

迁移示例：从T-SQL到PostgreSQL #

假设原T-SQL代码如下：

WITH XMLNAMESPACES (DEFAULT 'http://example.com/invoice')
INSERT INTO target_table (uuid, related_doc)
SELECT 
    x.value('(UUID/text())[1]', 'VARCHAR(50)') AS uuid,
    x.value('(DoctoRelacionado/text())[1]', 'VARCHAR(50)') AS related_doc
FROM source_table
CROSS APPLY xml_column.nodes('/Invoice') AS t(x);

对应PostgreSQL实现（单字段提取） #

-- 先禁用Schema验证
SET xmloption TO NOVALIDATE;

INSERT INTO target_table (uuid, related_doc)
SELECT 
    (xpath('/Invoice/UUID/text()', xml_column))[1]::text AS uuid,
    (xpath('/Invoice/DoctoRelacionado/text()', xml_column))[1]::text AS related_doc
FROM source_table;

带命名空间的XML解析 #

如果XML包含命名空间，可通过xpath的命名空间映射参数处理：

SET xmloption TO NOVALIDATE;

INSERT INTO target_table (uuid, related_doc)
SELECT 
    (xpath('/ns:Invoice/ns:UUID/text()', xml_column, ARRAY[ARRAY['ns', 'http://example.com/invoice']]))[1]::text AS uuid,
    (xpath('/ns:Invoice/ns:DoctoRelacionado/text()', xml_column, ARRAY[ARRAY['ns', 'http://example.com/invoice']]))[1]::text AS related_doc
FROM source_table;

使用xmltable更清晰映射字段 #

SET xmloption TO NOVALIDATE;

INSERT INTO target_table (uuid, related_doc)
SELECT x.uuid, x.related_doc
FROM source_table,
     xmltable(
         XMLNAMESPACES('http://example.com/invoice' AS ns),
         '/ns:Invoice' PASSING xml_column
         COLUMNS
             uuid VARCHAR(50) PATH 'ns:UUID',
             related_doc VARCHAR(50) PATH 'ns:DoctoRelacionado'
     ) AS x;

注意事项 #

• NOVALIDATE仅禁用Schema验证，若XML本身存在语法错误仍会报错。
• 批量处理时，只需在会话开始时执行一次SET xmloption命令即可。

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