← Back to team overview

maria-discuss team mailing list archive

Re: Connect engine assistance needed with nested first node in XML

 

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




Follow ups

References