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

Pavel Stehule wrote:
> 2009/6/1 Grzegorz Jaśkiewicz <gryzman@gmail.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