Insert XML from file in PostgreSQL

Question

I have several XML files and want to insert their content into a PostgreSQL table. This table has two columns - id (type serial) and a xml type column, in which I want to insert the content of one xml file (one row, one column = one xml file). In the documentation I haven't found how to insert xml from a file.

Is there some easy way how to do it?

Marek

Answer 1

Handily I just wrote an example of how to do this with plain text files that'll apply equally well to xml files. See the question updating table rows based on txt file.

It turns out you can do this with psql:

regress=> CREATE TABLE xmldemo(id serial primary key, blah xml);
regress=> \set test = `cat some.xml`
regress=> INSERT INTO xmldemo(blah) VALUES (:'test');
INSERT 0 1

If you're doing lots of this you might want to drive psql using a co-process or at least to generate SQL and pipe it into psql's stdin, so you don't have to do all that connection setup/teardown over and over.

Alternately, doing it with the shell:

#!/bin/bash
xmlfilename=$1
sep=$(printf '%04x%04x\n' $RANDOM $RANDOM)
psql <<__END__
INSERT INTO mytable(myxmlcolumn) VALUES (
\$x${sep}\$$(cat ${xmlfilename})\$x${sep}\$
);
__END__

The random separator generation is to protect against (unlikely) injection attacks that rely on knowing or guessing the dollar quoting separator tag.

You're going to be much saner and happier if you use a proper scripting language and PostgreSQL client library like Perl with DBI and DBD::Pg, Python with psycopg2 or Ruby with the Pg gem for any non-trivial work. Significant work with databases in the shell leads to pain, suffering, and excessive use of co-processes.