如何将指定结构的XML文件数据导入PostgreSQL数据库表

步骤1：创建目标数据表 #

首先创建匹配属性特征的存储表：

CREATE TABLE winscp_file_list (
    filename varchar(255) NOT NULL,
    type char(1) NOT NULL,
    size bigint NOT NULL,
    modification timestamptz NOT NULL,
    PRIMARY KEY (filename, modification)
);

步骤2：解析XML并插入数据 #

之前参考的公开示例失效的核心原因是该XML带默认命名空间http://winscp.net/schema/session/1.0，必须在XPath查询时显式指定命名空间映射才能正确定位节点。

以下是可直接运行的解析插入SQL：

WITH xml_input AS (
    -- 这里替换为你实际的XML内容，也可以用pg_read_file读取服务器上的XML文件
    SELECT '<?xml version="1.0" encoding="UTF-8"?>
<session xmlns="http://winscp.net/schema/session/1.0" name="KenWiens@205.138.249.107" start="2021-07-05T22:48:17.866Z">
  <ls>
    <destination value="/" />
    <files>
      <file>
        <filename value="ALL_ELAN_CLAIMS_20180601_20210531.txt" />
        <type value="-" />
        <size value="202286572" />
        <modification value="2021-06-03T15:45:25.000Z" />
        <permissions value="rw-rw-rw-" />
        <owner value="user" />
        <group value="group" />
      </file>
      <file>
        <filename value="ALL_ELAN_CLAIMS_20180701_20210630.txt" />
        <type value="-" />
        <size value="208553108" />
        <modification value="2021-07-03T12:16:37.000Z" />
        <permissions value="rw-rw-rw-" />
        <owner value="user" />
        <group value="group" />
      </file>
    </files>
  </ls>
</session>'::xml AS xml_content
),
file_nodes AS (
    -- 提取所有file节点，指定命名空间映射
    SELECT unnest(xpath(
        '//ns:file',
        xml_content,
        ARRAY[ARRAY['ns', 'http://winscp.net/schema/session/1.0']]
    )) AS file_node
    FROM xml_input
)
INSERT INTO winscp_file_list (filename, type, size, modification)
SELECT
    (xpath('./ns:filename/@value', file_node, ARRAY[ARRAY['ns', 'http://winscp.net/schema/session/1.0']])[1])::text AS filename,
    (xpath('./ns:type/@value', file_node, ARRAY[ARRAY['ns', 'http://winscp.net/schema/session/1.0']])[1])::text AS type,
    (xpath('./ns:size/@value', file_node, ARRAY[ARRAY['ns', 'http://winscp.net/schema/session/1.0']])[1])::text::bigint AS size,
    (xpath('./ns:modification/@value', file_node, ARRAY[ARRAY['ns', 'http://winscp.net/schema/session/1.0']])[1])::text::timestamptz AS modification
FROM file_nodes;

补充说明 #

• 如果XML文件存储在PostgreSQL服务器本地，可将xml_input中的XML字符串替换为pg_read_file('/path/to/your/file.xml', 0, 100000000)::xml，需确保文件路径对Postgres进程可读。
• 执行完插入语句后，可执行SELECT * FROM winscp_file_list;验证数据是否正确导入。

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