Access XML Tags with namespaces
How is it possible to access tags with namespaces (<ns:tag>) in a XML file?
Answer
With the Windows DOMDOC support, this can be done using the prefix in the tabname column option and/or field_format column option.
For instance, given the file gns.xml:
<?xml version="1.0" encoding="UTF-8"?>
<gpx xmlns:gns="http:dummy">
<gns:trkseg>
<trkpt lon="-121.9822235107421875" lat="37.3884925842285156">
<gns:ele>6.610851287841797</gns:ele>
<time>2014-04-01T14:54:05.000Z</time>
</trkpt>
<trkpt lon="-121.9821929931640625" lat="37.3885803222656250">
<ele>6.787827968597412</ele>
<time>2014-04-01T14:54:08.000Z</time>
</trkpt>
<trkpt lon="-121.9821624755859375" lat="37.3886299133300781">
<ele>6.771987438201904</ele>
<time>2014-04-01T14:54:10.000Z</time>
</trkpt>
</gns:trkseg>
</gpx>
and the defined CONNECT table:
CREATE TABLE xgns (
`lon` double(21,16) NOT NULL `field_format`='@',
`lat` double(20,16) NOT NULL `field_format`='@',
`ele` double(21,16) NOT NULL `field_format`='gns:ele',
`time` datetime date_format="YYYY-MM-DD 'T' hh:mm:ss '.000Z'"
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=XML `file_name`='gns.xml' tabname='gns:trkseg' option_list='xmlsup=domdoc';
select * from xgns;
Displays:
lon lat ele time
-121.9822235107421900 37.3884925842285160 6.6108512878417969 2014-04-01 14:54:05
-121.9821929931640600 37.3885803222656250 0.0000000000000000 2014-04-01 14:54:08
-121.9821624755859400 37.3886299133300780 0.0000000000000000 2014-04-01 14:54:10
Only the prefixed `ele` tag is recognized.
However this does not work with the libxml2 support. The solution is then to use a function ignoring the name space:
CREATE TABLE xgns2 (
`lon` double(21,16) NOT NULL `field_format`='@',
`lat` double(20,16) NOT NULL `field_format`='@',
`ele` double(21,16) NOT NULL `field_format`="*[local-name()='ele']",
`time` datetime date_format="YYYY-MM-DD 'T' hh:mm:ss '.000Z'"
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=XML `file_name`='gns.xml' tabname="*[local-name()='trkseg']" option_list='xmlsup=libxml2';
select * from xgns2;
Displays:
lon lat ele time
-121.9822235107421900 37.3884925842285160 6.6108512878417969 2014-04-01 14:54:05
-121.9821929931640600 37.3885803222656250 6.7878279685974121 2014-04-01 14:54:08
-121.9821624755859400 37.3886299133300780 6.7719874382019043 2014-04-01 14:54:10
This time, all `ele` tags are recognized. This solution does not work with DOMDOC.
