SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Mar 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Export XML Data to a MySQL DB

    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. #2
    SitePoint Member
    Join Date
    Mar 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    think about how do you want to load xml file into mysql DB
    do you want to load them as blob?

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Tags for this Thread

Bookmarks

Posting Permissions

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