Load simple XML file into Postgres<>
This question already has an answer here:
- XML data to PostgreSQL database 3 answers
Well, it can be done. I wrote a complete function for the same purpose a while back:
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+.