Re: Import data from XML file

We've used Pentaho Data Integration (aka Kettle) at http://kettle.pentaho.org to
do this in the past. (Kettle is the free / open source version, although there
is a bigger commercial version of PDI that does more.) It reads XML nicely and
talks natively to PostgreSQL databases, so you can load data directly from your
XML file right into your PostgreSQL database.

If you're a Microsoft shop, you can also use SQL Server Integration Services
[SSIS] to do the same thing but much better and faster, although I almost
hesitate to mention a Microsoft tool on this forum even though it's just going
from XML to PostgreSQL and CSV with no trace of SQL Server anywhere to be seen.
(If you do go the SSIS route, the "dotConnect for PostgreSQL" ADO.NET driver
from Devart [ http://www.devart.com/dotconnect/postgresql/ ] works wonderfully
to connect from SSIS to PostgreSQL. Unfortunately, the Npgsql driver doesn't
really work very well with SSIS...)

- Bill

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Erwin Brandstetter
> Sent: Wednesday, August 26, 2009 12:10 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Import data from XML file
>
> Hi!
>
> How do you import data from an xml-file?
> For instance, if I have a file like this:
>
> <?xml version="1.0" encoding="utf-8"?>
> <p_update>
> <main_categories>
> <main_category>
> <main_category_name>Sonstiges</main_category_name>
> <main_category_id>5</main_category_id>
> </main_category>
> <main_category>
> <main_category_name>Buehne</main_category_name>
> <main_category_id>2</main_category_id>
> </main_category>
> <main_category>
> <main_category_name>Konzerte</main_category_name>
> <main_category_id>1</main_category_id>
> </main_category>
> </main_categories>
> <categories>
> <category>
> <category_name>Reggae</category_name>
> <main_category_id>1</main_category_id>
> <category_id>45</category_id>
> </category>
> <category>
> <category_name>sonstige</category_name>
> <main_category_id>5</main_category_id>
> <category_id>44</category_id>
> </category>
> </categories>
> </p_update>
>
>
> ... and I want a CSV file like this:
>
> main_category_name main_category_id
> Sonstiges 5
> Buehne 2
>
> category_name main_category_id category_id
> Reggae 1 45
> sonstige 5 44
>
>
> Or is there a way to import directly into tables in a postgres
> database?
>
>
> Your help would be appreciated!
> Regards
> Erwin
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general