Export XML Data to a MySQL DB

HTML & CSS
1

Hi I have a XML file which I created from the overpass api. I’d like to load this data from this file into a mysql database. I’ve been using this code to test, but none of the data loads.

LOAD XML LOCAL INFILE ‘/home/jay/Downloads/interpreter1’
into table jayDB.xml1 (id);

I’m new to XML so not sure if this is possible.

here is some sample data from the file. What I’d like populated is: id,lat,lon,addr:housenumber, etc

<osm version=“0.6” generator=“Overpass API”>
<note>
The data included in this document is from www.openstreetmap.org. The data is made available under ODbL.
</note>
<meta osm_base=“2013-03-07T14:54:02Z” areas=“2013-03-07T10:37:02Z”/>
<node id=“240486180” lat=“50.9744274” lon=“3.0152858”>
<tag k=“addr:housenumber” v=“9”/>
<tag k=“addr:street” v=“Marktplaats”/>
<tag k=“amenity” v=“cafe”/>
<tag k=“email” v=“vandaelekoen67@skynet.be”/>
<tag k=“name” v=“Paviljoentje”/>
<tag k=“opening_hours” v=“Mo-Su 09:00+; Tu off; Th 09:00-14:00”/>
<tag k=“phone” v=“+3251636211”/>
<tag k=“website” v=“http://www.paviljoentjestaden.be”/>
</node>
<node id=“244312208” lat=“51.2461401” lon=“5.4390455”>
<tag k=“amenity” v=“cafe”/>
<tag k=“created_by” v=“JOSM”/>
<tag k=“name” v=“De Club”/>
</node>
<node id=“250418155” lat=“51.3292376” lon=“4.6355511”>
<tag k=“amenity” v=“cafe”/>
<tag k=“created_by” v=“JOSM”/>
<tag k=“name” v=“Trapke op”/>
</node>
<node id=“250418156” lat=“51.3284894” lon=“4.6355746”>
<tag k=“amenity” v=“cafe”/>
<tag k=“created_by” v=“JOSM”/>
<tag k=“name” v=“De Goeden Tijd”/>
</node>
<node id=“254852601” lat=“51.3202164” lon=“4.5068222”>
<tag k=“amenity” v=“cafe”/>
<tag k=“created_by” v=“JOSM”/>
<tag k=“name” v=“Las Vegas”/>
</node>
<node id=“255534757” lat=“51.4297430” lon=“5.0309450”>
<tag k=“amenity” v=“cafe”/>
<tag k=“created_by” v=“JOSM”/>
<tag k=“name” v=“'t Hegse Stalleke”/>
</node>
<node id=“256147792” lat=“50.9134966” lon=“4.5729376”>
<tag k=“amenity” v=“cafe”/>
<tag k=“cuisine” v=“regional”/>
<tag k=“name” v=“Leliehof”/>
<tag k=“sport” v=“tennis”/>
</node>
<node id=“256151689” lat=“50.9189697” lon=“4.5640453”>
<tag k=“amenity” v=“cafe”/>
<tag k=“created_by” v=“JOSM”/>
<tag k=“name” v=“De Zes Linden”/>
</node>
<node id=“261984618” lat=“51.2922877” lon=“4.5051107”>
<tag k=“amenity” v=“cafe”/>
<tag k=“created_by” v=“JOSM”/>
<tag k=“name” v=“'t Zand”/>
</node>

2

I’ve made some progess load xml LOCAL INFILE ‘/home/jay/Downloads/interpreter1’ into table jayDB.xml2 rows identified by ‘<node>’;

however i think due to the format of the file it thinks K and V are the columns. Where as I was amenity to be column and cafe to be the value

3

think about how do you want to load xml file into mysql DB
do you want to load them as blob?

4

https://dev.mysql.com/doc/refman/5.5/en/load-xml.html

I don’t think you’ll be able to read the ‘tag’ elements from your xml.