Load simple XML file into Postgres

Question

I have a file with a simple xml structure that I want to load into a Postgres table.

<rows>
<field name="id">1</field>
<field name="age">75-84</field>
<field name="gndr">F</field>
<field name="inc">32000-47999</field>
</rows>

Is there an easy way of doing this?

Are you trying to throw it into a blob, or parse it out into a normalized table structure? What tool do you want to use (Perl, Java, etc.)? — jjanes, Mar 31 '14 at 22:12
Trying to parse into a normalized table. If PostGres doesn't have a function to take care of the parsing, Python would be my first choice. — ADJ, Mar 31 '14 at 22:20

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

Well, it can be done. I wrote a complete function for the same purpose a while back:

XML data to PostgreSQL database

I use pg_read_file() to read in the whole file into a variable in plpgsql and proceed from there. This carries a few restrictions:

Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files. Use of these functions is restricted to superusers.

But I listed alternatives.

If you can convert the XML file to JSON it gets simpler. There are a bunch of tools for that available.

Starting with Postgres 9.3 you get pretty good support for the json data type.
Better yet, use the jsonb data type in Postgres 9.4+.

How do I query using fields inside the new PostgreSQL JSON datatype?

Load simple XML file into Postgres

1 Answers1