Re: 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/