Insert XML from file in PostgreSQL
file, insert, postgresql, xml
Solution
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.
Problem
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