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-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Erwin Brandstetter > Sent: Wednesday, August 26, 2009 12:10 PM > To: pgsql-general@postgresql.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
