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

is there any way currently to convert xml file in format like below,
to a table ?


<foo>
  <section>
    <ssc id="foo1">
       <foo_data a="1" b="2"/>
       <foo_more_data c="a" d="b"/>
    </ssc>
  </section>

  <section>
    <ssc id="foo2">
        ...
    </ssc>
  </section>

  <section>
...
  </section>

  <section>
...
  </section>
</foo>



--
GJ

On Mon, Jun 01, 2009 at 10:53:08AM +0100, Grzegorz Jaaakiewicz wrote:
> is there any way currently to convert xml file in format like below,
> to a table ?

I've had good luck with the xpath support in PG[1] and some variant of
the "unnest" function that's in PG 8.4 (various versions[2] have been
posted to this list for older versions).

--
  Sam  http://samason.me.uk/

 [1] http://www.postgresql.org/docs/current/static/functions-xml.html#FUNCTIONS-XML-PROCESSING
 [2] http://archives.postgresql.org/message-id/b88c3460905290021o6870bb46tec88ced0c40643d9@mail.gmail.com

xpath is fine, but not when you have 10+ fields to extract ;)

On Mon, Jun 01, 2009 at 11:22:14AM +0100, Grzegorz Jaaakiewicz wrote:
> xpath is fine, but not when you have 10+ fields to extract ;)

I've got a few views pulling 10 to 15 values out of XML files and
it works OK, not amazing performance but for what I'm doing it's no
problem.  Scaling beyond that would seem to start getting somewhat
unmaintainable, but it seems to be working out that the functional
dependencies put tight constraints on what I can put into a table and
hence the number of values I pull out in one go.

Worst case you could always generate the SQL from other code, or even do
the parsing from XML to something more structured outside PG.

--
  Sam  http://samason.me.uk/

Hello

you can use simple perl parser

an sample is on
http://www.postgres.cz/index.php/PL/Perlu_-_Untrusted_Perl#Generov.C3.A1n.C3.AD.2C_zpracov.C3.A1n.C3.AD_XML

code is in english and perl, description in czech, sorry

regards
Pavel Stehule

2009/6/1 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
> xpath is fine, but not when you have 10+ fields to extract ;)
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

That's one of things pg xml type lacks ... :/
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.

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.
>

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

On Monday 01 June 2009 12:53:08 Grzegorz Jaśkiewicz wrote:
> is there any way currently to convert xml file in format like below,
> to a table ?

I have some code that does this, but it was written a long time ago and will
probably need some polishing.

One main problem is how you specify that exact mapping mechanism.  AFAIR, my
code accepted as input the exact format that table_to_xml etc. put out (and
probably crashes otherwise).  In reality you probably want an XSLT
transformation in between.  Which would require a smoother XSLT integration
into the backend code.

I think this is something we could work on for 8.5.

I can test/review the code, if you want. It would be a nice thing to
have in postgresql, obviously once you prepare statement enough to
convince Tom :)
XSLT is a necessity , otherwise it won't be standard, and thus - quite useless.