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>
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
found this interesting thing: http://stackoverflow.com/questions/5491056/how-to-import-xml-file-into-mysql-database-table-using-xml-load-function
have an XML file which looks like this :
<?xml version="1.0" encoding="UTF-8"?>
<resultset statement="YOUR SQL STATEMENTS TO GENERATE THIS XML FILE" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="personal_number">539</field>
<field name="firstname">Name</field>
<field name="lastname">Surname</field>
<field name="email">email.domain.com</field>
<field name="start_time">2011-04-02 13:30:00</field>
<field name="end_time">2011-04-02 18:15:00</field>
<field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
</row>
<row>
<field name="personal_number">539</field>
<field name="firstname">Name</field>
<field name="lastname">Surname</field>
<field name="email">email.domain.com</field>
<field name="start_time">2011-04-02 13:30:00</field>
<field name="end_time">2011-04-02 18:15:00</field>
<field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
</row>
<row>
<field name="personal_number">539</field>
<field name="firstname">Name</field>
<field name="lastname">Surname</field>
<field name="email">email.domain.com</field>
<field name="start_time">2011-04-02 13:30:00</field>
<field name="end_time">2011-04-02 18:15:00</field>
<field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
</row>
I am trying to import it in MySQL using SQL statement :
use databasename;
LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename;
The table my_tablename has the following fields :
id (auto increment id)
personal_number(varchar)
firstname(varchar)
lastname(varchar)
email(varchar)
start_time(varchar)
end_time(varchar)
employee_category(varchar)
I get error : Error Code: 1136 Column count doesn’t match value count at row 1
I am using MySQL 5.1.56
I assume this error occurs because the database table has field id, which is not present in the XML file. How is it possible to import this XML file using MySQL queries of built in functions such that it skips id column during the import and relies on the auto increment function for the id column? Is there some smarter way of handling XML file imports im MySQL? Maybe there is better statement which allows to specify column mapping?
we can specify fields like this:
LOAD XML LOCAL INFILE '/pathtofile/file.xml'
INTO TABLE my_tablename(personal_number, firstname, ...);
since ID is auto increment, you can also specify ID=NULL as,
LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename SET ID=NULL;