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/