Results 1 to 3 of 3

Thread: Export XML Data to a MySQL DB

  1. #1

    Default 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



    Code:
    <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
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  2. #2

    Default Re: Export XML Data to a MySQL DB

    found this interesting thing: http://stackoverflow.com/questions/5...-load-function

    have an XML file which looks like this :

    Code:
        <?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 :

    Code:
    use databasename;
    LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename;
    The table my_tablename has the following fields :

    Code:
    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:

    Code:
    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,
    Code:
     LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename SET ID=NULL;
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  3. #3

    Default Re: Export XML Data to a MySQL DB


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •