maria-discuss team mailing list archive

[Olivier Bertrand <bertrandop@xxxxxxxxx>]

Le 27/01/2014 20:37, Rasmus Johansson a écrit :
> Hi,
>
> I'm trying to parse GPX files, which are XML with the connect engine.
> They look like this:
>
> <?xml version="1.0" encoding="UTF-8"?>
> <gpx version="1.1" creator="runtastic - makes sports funtastic,
> http://www.runtastic.com";>
>    <trk>
>      <trkseg>
>          <trkpt lon="-0.7057708501815800" lat="51.4470825195312003">
>                  <ele>76.6089172363281</ele>
>                  <time>2013-08-12T18:17:25.000Z</time>
>          </trkpt>
>          <trkpt lon="-0.7059442996978760" lat="51.4470443725586009">
>                  <ele>72.1513061523437</ele>
>                  <time>2013-08-12T18:17:28.000Z</time>
>          </trkpt>
>          <trkpt lon="-0.7059520483016970" lat="51.4470558166503977">
>                  <ele>75.0039749145508</ele>
>                  <time>2013-08-12T18:17:30.000Z</time>
>          </trkpt>
>      </trkseg>
>    </trk>
> </gpx>
>
> Once I have the Connect -engine enabled I do the following:
>
> create table GPXSource (
>     lat char(20) field_format='@',
>     lon char(20) field_format='@')
>     engine=CONNECT table_type=XML
> file_name='/home/rasmus/nodegpxmariadb/public/uploads/runtastic_20130812_2157_Running.gpx'
> tabname='gpx/trk/trkseg'
>     option_list='rownode=trkpt';
>
> It doesn't work, I cannot figure out how to tell the Connect engine
> that the data I'm interested is beneath the gpx/trk/trkseg -node
> (tabname). If I remove the trk and trkseg -nodes from the data and
> just have the gpx -node left and all trkpt -nodes inside it everything
> works perfectly. Any help is appriciated.
>
> BR,
> Rasmus
>
> --
>
> Rasmus Johansson, VP Engineering
> MariaDB | t: +358 50 499 9589 | Skype: ratzpo
Thank you for reporting this. It is a bug coming from XML table still 
looking for the deprecaed option 'table_name'
instead of 'tabname'. It now fixed in the future CONNECT versions.

Meanwhile, you can do one of the following:

1) Using table_name instead of tabname. This is not a declared option 
and therefore must be used in the table list:

create table GPXSource (
   lat char(20) field_format='@',
   lon char(20) field_format='@')
   engine=CONNECT table_type=XML
file_name='/home/rasmus/nodegpxmariadb/public/uploads/runtastic_20130812_2157_Running.gpx'
   option_list='table_name=trkseg,rownode=trkpt';

Note 1: 'gpx/trk/trkseg' was wrong, gpx not being the top node of the 
file. '/gpx/trk/trkseg' or '//gpx/trk/trkseg'
could have been used but it is easier to just say 'trkseg' internally 
transformed by CONNECT in '//trkseg'.

Note 2: 'rownode=trkpt' is not required, the row nodes being by default 
the table node children.

2) The table node being by default the name of the table, you can 
alternatively do something like:

create table trkseg (
   lat double(20,16) field_format='@',
   lon double(20,16) field_format='@',
   ele double(17,13),
   time datetime date_format="YYYY-MM-DD 'T' hh:mm:ss '.000Z'")
   engine=CONNECT table_type=XML
file_name='/home/rasmus/nodegpxmariadb/public/uploads/runtastic_20130812_2157_Running.gpx';

calling your table by the table node name.

Olivier