openstreetmap - Export XML Data to a MySQL DB

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




The data included in this document is from www.openstreetmap.org. The data is made available under ODbL.

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

I tried to look at the details https://dev.mysql.com/doc/refman/5.5/en/load-xml.html

Answer

MySQL allows only the xml formats as stated in the linked document. You have to modify the osm xml into one of those.

Alternatively, you can use osmosis or GDAL to do the import.

See this Q&A for some guidance:

https://stackoverflow.com/questions/20125242/import-openstreetmap-data-osm-file-to-a-mysql-database-sql-file

http://forum.openstreetmap.org/viewtopic.php?pid=152695

http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/