导入XML文件到PostgreSQL [英] Import XML files to PostgreSQL

问题描述

我确实有很多的XML文件，我想在表格中导入 xml_data ：

I do have a lot of XML files I would like to import in the table xml_data:

create table xml_data(result xml);

要做到这一点我有循环一个简单的bash脚本：

To do this I have a simple bash script with loop:

#!/bin/sh
FILES=/folder/with/xml/files/*.xml
for f in $FILES
do
  psql psql -d mydb -h myhost -U usr -c \'\copy xml_data from $f \'
done

然而，这会尝试导入每一个文件作为单独的行的每一行。这将导致错误：

However this will try to import each line of every file as separate row. This leads to error:

ERROR:  invalid XML content
CONTEXT:  COPY address_results, line 1, column result: "<?xml version="1.0" encoding="UTF-8"?>"

我明白为什么会失败，但无法弄清楚如何使 \\复制来一次导入整个文件分为单列。

I understand why it fails, but cannot figure out how to make \copy to import the whole file at once into single row.

推荐答案

我会尝试不同的方法：读取XML文件直接插入PLPGSQL函数内部变量，然后从那里继续。应快了很多并不少更强劲。你需要超级用户权限，但。

I would try a different approach: read the XML file directly into variable inside a plpgsql function and proceed from there. Should be a lot faster and a lot more robust. You need superuser privileges, though.

CREATE OR REPLACE FUNCTION f_sync_from_xml()
  RETURNS boolean AS
$BODY$
DECLARE
    myxml    xml;
    datafile text := 'path/to/my_file.xml';
BEGIN

myxml := pg_read_file(datafile, 0, 100000000);  -- arbitrary 100 MB max.

CREATE TEMP TABLE tmp AS
SELECT (xpath('//some_id/text()', x))[1]::text AS id
FROM   unnest(xpath('/xml/path/to/datum', myxml)) x;
...

查找与此密切相关的答案解释，并链接一个完整的code例如：

Find a complete code example with explanation and links in this closely related answer:

• XML数据PostgreSQL数据库

• XML data to PostgreSQL database

这篇关于导入XML文件到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！