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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
