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

What language and client library are you using? PostgreSQL version? — Craig Ringer, Mar 25 '13 at 09:27
PostgreSQL 9.0.7. The database will be accessed from php code (php 5.3). Does it mean that there is no simple (built in PostgreSQL) way how to load xml file content into database table column? — Marek Hajžman, Mar 25 '13 at 09:36
This is usually done by the client software, but I can't think of a simple way doing this with `psql`. Which SQL client are you using? — a_horse_with_no_name, Mar 25 '13 at 09:52
I use psql under SUSE linux (SLED) or can use pgAdmin III v1.16.1 under Windows. — Marek Hajžman, Mar 25 '13 at 10:03

score 5 · Accepted Answer · edited May 23 '17 at 10:30

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.

Another option would be to use SQL Workbench/J which supports an "extended" syntax like: `insert into foo (id, content) values (42, {$clobfile='/foo/bar.xml')`. — a_horse_with_no_name, Mar 25 '13 at 10:25
And what about this? If it works with text files, it should work also with xml files http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file?rq=1 I mean the use of \set content `cat file` INSERT INTO testtable VALUES(15, :'content'); — Marek Hajžman, Mar 25 '13 at 10:33

Insert XML from file in PostgreSQL

1 Answers1

Linked