psql导入文件_导入XML文件到PostgreSQL

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

create table xml_data(result xml);

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.

解决方案

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;

...

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