Export XML Data to a MySQL DB

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

Export XML Data to a MySQL DB

Postby unleash » 30. April 2014 02:22

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

Code: Select all
<osm version="0.6" generator="Overpass API">
The data included in this document is from www.openstreetmap.org. The data is made available under ODbL.
<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 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"/>

how to proceed

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

i tried to look atthe details https://dev.mysql.com/doc/refman/5.5/en/load-xml.html
Posts: 141
Joined: 03. December 2011 10:16
Operating System: OpenSuse Linux 12.1

Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 1 guest