Re: xml to table (as oppose to table to xml)

From: Scott Bailey <artacus(at)comcast(dot)net>
To:
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: xml to table (as oppose to table to xml)
Date: 2009-06-01 22:05:02
Message-ID: 4A24508E.8020901@comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Pavel Stehule wrote:
> 2009/6/1 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
>> That's one of things pg xml type lacks ... :/
>
> yes - SQL/XML isn't completed yet
>
> http://wiki.postgresql.org/wiki/XML_Support :(
>
> I believe so some procedure like xml_to_table should be nice.
>
> but plperlu code should be simple (as perl code should be :)) and fast
>
>
>
>> I just need that to get some real xml, and convert to table once, so I
>> should be fine with xpath, but ... heh. This is so ugly.

I started to blog on exactly this but didn't get around to finishing it.
Seeing the article on Postgres Online Journal reminded me how clunky the
original pg style is. This is an Postgres adaption of an Oracle
function. It should make your queries cleaner.

CREATE OR REPLACE FUNCTION extract_value(
VARCHAR,
XML
) RETURNS text AS
$$
SELECT CASE WHEN $1 ~ '@[[:alnum:]_]+$'
THEN (xpath($1, $2))[1]
WHEN $1 ~* '/text()$'
THEN (xpath($1, $2))[1]
WHEN $1 LIKE '%/'
THEN (xpath($1 || 'text()', $2))[1]
ELSE (xpath($1 || '/text()', $2))[1]
END::text;
$$ LANGUAGE 'sql' IMMUTABLE;

Use it like so:

SELECT extract_value('/wpt/name', node) AS name,
extract_value('@lon', node)::numeric AS lon,
extract_value('@lat', node)::numeric AS lat,
extract_value('/wpt/ele', node)::numeric AS ele
FROM (
SELECT unnest(xpath('/gpx/wpt', object_value)) AS node
FROM gpx
WHERE object_name = 'fellsLoop'
) sub

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Sven W 2009-06-01 22:29:31 pl/pgsql FOUND variables and RULES
Previous Message Dave Clarke 2009-06-01 21:42:29 Foreign Key question