Find the answer to your Linux question:
Results 1 to 1 of 1
hello dear linuxfriends - and experts, how to store this in mysql Code: osm version="0.6" generator="Overpass API"> <note> The data included in this document is from www.openstreetmap.org. The data is ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Linux Newbie
    Join Date
    May 2013
    Posts
    192

    XML:Twig and XML:Simple - transfer xml for storing in mysql database


    hello dear linuxfriends - and experts,

    how to store this in mysql
    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>
    this is a question regarding textmangling and converting.
    well i want to use perl for text-mangling - so we can use the

    Code:
    XML::Simple module. here's an example of a little script to parse your XML:
    
    see the Code:
    
    #!/usr/bin/perl
    use strict;
    use warnings;
    use XML::Simple;
    use Data::Dumper;
    
    my $xmlfile = shift || die "Usage: $0 <XML_FILE>\n";
    
    my $ref;
    eval {
      $ref = XMLin($xmlfile,
        ForceArray    => 0,
        KeyAttr       => [ ],
        SuppressEmpty => '',
      ) or die "Can't read XML from $xmlfile: $!\n";
    
    };
    die $@ if($@);
    print Dumper $ref;

    Explantion: iterating trough the array / hash - this does create a file and helps carving up the data
    into comma separated lines of data than can be redirected to file.

    see another example:

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <root>
    <node id="297467" lat="49.5014" lon="8.1465">
        <tag k="addr:city" v="Stuttgart"/>
        <tag k="addr:housenumber" v="23"/>
        <tag k="addr:postcode" v="69115"/>
        <tag k="addr:street" v="Sofienstrae"/>
        <tag k="name" v="ARLT"/>
        <tag k="phone" v="+49 6221 20229"/>
        <tag k="shop" v="computer"/>
        <tag k="source" v="survey"/>
        <tag k="website" v="http://www.arlt.com"/>
        <tag k="wheelchair" v="yes"/>
    </node>
    <node id="305144906" lat="49.40012" lon="8.6929652">
    <tag k="addr:city" v="Mainz"/>
    <tag k="addr:country" v="DE"/>
    <tag k="addr:housenumber" v="13-15"/>
    <tag k="addr:postcode" v="69115"/>
    <tag k="addr:state" v="Baden-WUrttemberg"/>
    <tag k="addr:street" v="Rohrbacher StraSSe"/>
    <tag k="name" v="Heidel-bike"/>
    <tag k="opening_hours" v="Tu-Fr 10:00-18:30; Sa 10:00-14:00"/>
    <tag k="shop" v="bicycle"/>
    <tag k="website" v="http://www.heidelbike.de/"/>
    <tag k="wheelchair" v="yes"/>
    </node>
    </root>
    __OUTPUT__


    Code:
    #ID, LAT, LON, CITY, HOUSNUMBER, POSTCODE, STREET, NAME, PHONE, SHOP, SOURCE, WEBSITE, WHEELCHAIR
    297467, 49.5014, 8.1465, Stuttgart, 23, 69115, Sofienstrae, ARLT, +49 6221 20229, computer, survey, http://www.arlt.com,
     yes,
    305144906, 49.40012, 8.6929652, Mainz, DE, 13-15, 69115, Baden-WUrttemberg, Rohrbacher StraSSe, Heidel-bike, Tu-Fr 10:00
    -18:30; Sa 10:00-14:00, bicycle, http://www.heidelbike.de/, yes,
    another suggstion: XML::Twig

    well i am not an XML expert so i confess i found some probelm in the data example, german char included..
    I put the modified data in a file. We can see the content after the __END__ token in the script:
    Code:
    use strict;
    use warnings;
    use XML::Twig;
    
    my $t= XML::Twig->new( pretty_print => 'indented',
                        twig_handlers => {
    
                              'node'=>sub{print $_[1]->att('id'),', ',$_[1]->att('lat'),', ',$_[1]->att('lon'),', ' ;
    
                                          foreach my $tag ( $_[1]->children ){
                                                  print $tag->att('v').", ";
                                          }
                                          print "\n";
                              },
    
                        }
    );
    
    print "#ID, LAT, LON, CITY, HOUSNUMBER, POSTCODE, STREET, NAME, PHONE, SHOP, SOURCE, WEBSITE, WHEELCHAIR\n";
    $t->parsefile('xml-001.xml');


    which method is the best one - love to hear from you

    greetinss
    Last edited by sayhello; 05-14-2014 at 07:05 PM.

Posting Permissions

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