[Maria-discuss] Connect engine assistance needed with nested first node in XML
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
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
Thank you Olivier! While you're looking at it I could actually report another problem I noticed. To have multiple files, e.g. running*.xml with Multiple=1 doesn't seem to work with XML files. Always an empty result although reading 1 file works perfectly well.
BR, Rasmus --
Rasmus Johansson, VP Engineering MariaDB | t: +358 50 499 9589 | Skype: ratzpo
On Tue, Jan 28, 2014 at 2:27 PM, Olivier Bertrand bertrandop@gmail.com wrote:
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
Le 28/01/2014 13:48, Rasmus Johansson a écrit :
Thank you Olivier! While you're looking at it I could actually report another problem I noticed. To have multiple files, e.g. running*.xml with Multiple=1 doesn't seem to work with XML files. Always an empty result although reading 1 file works perfectly well.
Sure enough, Multiple is not implemented for XML files. I'll check whether it is compatible with them and if so, I shall implement it.
Unfortunately, even I can do it, you cannot do anything about it but wait for future versions of CONNECT.
Olivier
BR, Rasmus --
Rasmus Johansson, VP Engineering MariaDB | t: +358 50 499 9589 | Skype: ratzpo
On Tue, Jan 28, 2014 at 2:27 PM, Olivier Bertrand bertrandop@gmail.com wrote:
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
On Tue, Jan 28, 2014 at 4:59 PM, Olivier Bertrand bertrandop@gmail.com wrote:
Le 28/01/2014 13:48, Rasmus Johansson a écrit :
Thank you Olivier! While you're looking at it I could actually report another problem I noticed. To have multiple files, e.g. running*.xml with Multiple=1 doesn't seem to work with XML files. Always an empty result although reading 1 file works perfectly well.
Sure enough, Multiple is not implemented for XML files. I'll check whether it is compatible with them and if so, I shall implement it.
Unfortunately, even I can do it, you cannot do anything about it but wait for future versions of CONNECT.
OK, thanks. I think we need to document that it's not supported for XML. I'll do that. But I do think it would be very useful to read XML files with the same schema combining them all in queries through CONNECT.
BR, Rasmus
Le 28/01/2014 16:32, Rasmus Johansson a écrit :
On Tue, Jan 28, 2014 at 4:59 PM, Olivier Bertrand bertrandop@gmail.com wrote:
Le 28/01/2014 13:48, Rasmus Johansson a écrit :
Thank you Olivier! While you're looking at it I could actually report another problem I noticed. To have multiple files, e.g. running*.xml with Multiple=1 doesn't seem to work with XML files. Always an empty result although reading 1 file works perfectly well.
Sure enough, Multiple is not implemented for XML files. I'll check whether it is compatible with them and if so, I shall implement it.
Unfortunately, even I can do it, you cannot do anything about it but wait for future versions of CONNECT.
OK, thanks. I think we need to document that it's not supported for XML. I'll do that. But I do think it would be very useful to read XML files with the same schema combining them all in queries through CONNECT.
BR, Rasmus
I have implemented Multiple for XML files. It will be added in the next version.
Olivier
nice news :) i will use it!
2014-01-28 Olivier Bertrand bertrandop@gmail.com:
Le 28/01/2014 16:32, Rasmus Johansson a écrit :
On Tue, Jan 28, 2014 at 4:59 PM, Olivier Bertrand bertrandop@gmail.com wrote:
Le 28/01/2014 13:48, Rasmus Johansson a écrit :
Thank you Olivier! While you're looking at it I could actually report another problem I noticed. To have multiple files, e.g. running*.xml with Multiple=1 doesn't seem to work with XML files. Always an empty result although reading 1 file works perfectly well.
Sure enough, Multiple is not implemented for XML files. I'll check whether it is compatible with them and if so, I shall implement it.
Unfortunately, even I can do it, you cannot do anything about it but wait for future versions of CONNECT.
OK, thanks. I think we need to document that it's not supported for XML. I'll do that. But I do think it would be very useful to read XML files with the same schema combining them all in queries through CONNECT.
BR, Rasmus
I have implemented Multiple for XML files. It will be added in the next version.
Olivier
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
I have pushed the new CONNECT version today. It does what is described below Olivier
Le 29/01/2014 01:16, Roberto Spadim a écrit :
nice news :) i will use it!
2014-01-28 Olivier Bertrand bertrandop@gmail.com:
Le 28/01/2014 16:32, Rasmus Johansson a écrit :
On Tue, Jan 28, 2014 at 4:59 PM, Olivier Bertrand bertrandop@gmail.com wrote:
Le 28/01/2014 13:48, Rasmus Johansson a écrit :
Thank you Olivier! While you're looking at it I could actually report another problem I noticed. To have multiple files, e.g. running*.xml with Multiple=1 doesn't seem to work with XML files. Always an empty result although reading 1 file works perfectly well.
Sure enough, Multiple is not implemented for XML files. I'll check whether it is compatible with them and if so, I shall implement it.
Unfortunately, even I can do it, you cannot do anything about it but wait for future versions of CONNECT.
OK, thanks. I think we need to document that it's not supported for XML. I'll do that. But I do think it would be very useful to read XML files with the same schema combining them all in queries through CONNECT.
BR, Rasmus
I have implemented Multiple for XML files. It will be added in the next version.
Olivier
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Le 29/01/2014 01:16, Roberto Spadim a écrit :
nice news :) i will use it!
It's done in the new version of CONNECT that will be available for MariaDB 10.0.9
2014-01-28 Olivier Bertrand bertrandop@gmail.com:
Le 28/01/2014 16:32, Rasmus Johansson a écrit :
On Tue, Jan 28, 2014 at 4:59 PM, Olivier Bertrand bertrandop@gmail.com wrote:
Le 28/01/2014 13:48, Rasmus Johansson a écrit :
Thank you Olivier! While you're looking at it I could actually report another problem I noticed. To have multiple files, e.g. running*.xml with Multiple=1 doesn't seem to work with XML files. Always an empty result although reading 1 file works perfectly well.
Sure enough, Multiple is not implemented for XML files. I'll check whether it is compatible with them and if so, I shall implement it.
Unfortunately, even I can do it, you cannot do anything about it but wait for future versions of CONNECT.
OK, thanks. I think we need to document that it's not supported for XML. I'll do that. But I do think it would be very useful to read XML files with the same schema combining them all in queries through CONNECT.
BR, Rasmus
I have implemented Multiple for XML files. It will be added in the next version.
Olivier
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Olivier Bertrand -
Rasmus Johansson -
Roberto Spadim