Re: Import data from XML file

Erwin

did you try ems-data?
http://ems-data-import-2007-for-postgresql.software.informer.com/3.0/

Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Wed, 26 Aug 2009 11:54:23 -0700
> From: artacus(at)comcast(dot)net
> CC: pgsql-general(at)postgresql(dot)org
> Subject: Re: [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
> >
>
> Not sure why you are mentioning a CSV export. I ASSUME you want to
> import into database tables and not go directly to csv. (If that's the
> case, use another tool, not a database.)
>
> INSERT INTO main_categories(name, id)
> SELECT extract_value('//main_category_name', x) AS name,
> extract_value('//main_category_id', x)::int AS id
> -- without extract_value
> -- CAST(CAST(xpath('//main_category_id/text()', x)[0] AS varchar) AS
> int) AS id
> FROM unnest(xpath('//main_category', xml('...your xml here...'))) x
>
> INSERT INTO categories(name, main_id, id)
> SELECT extract_value('//category_name', x) AS name,
> extract_value('//main_category_id', x)::int AS main_id,
> extract_value('//category_id', x)::int AS id
> FROM unnest(xpath('//category', xml('...your xml here...'))) x
>
> Unnest isn't included until pg 8.4. And extract_value() is a function I
> borrowed from Oracle to make life easier. I have a write up about it on
> my blog.
>
> http://scottrbailey.wordpress.com/2009/06/19/xml-parsing-postgres/
>
>
> --
> 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

_________________________________________________________________
Windows Live: Keep your friends up to date with what you do online.
http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:ON:WL:en-US:SI_SB_online:082009