text mangling with the perl-module XML-Simple - Joomla! Forum - community, help and support

hello dear perl-experts,


i need transforme xml-data mysql db -

code: select all

<?xml version="1.0" encoding="utf-8"?>
<osm version="0.6" generator="overpass api">
<note>the data included in document www.openstreetmap.org. data made available under odbl.</note>
<meta osm_base="2014-04-27t13:49:02z"/>

  <node id="297489767" lat="49.4085014" lon="8.6941465">
    <tag k="addr:city" v="heidelberg"/>
    <tag k="addr:housenumber" v="23"/>
    <tag k="addr:postcode" v="69115"/>
    <tag k="addr:street" v="sofienstraße"/>
    <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.4060012" lon="8.6929652">
    <tag k="addr:city" v="heidelberg"/>
    <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-württemberg"/>
    <tag k="addr:street" v="rohrbacher straße"/>
    <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>
  <node id="305963167" lat="49.4139877" lon="8.6924247">
    <tag k="addr:city" v="heidelberg"/>
    <tag k="addr:country" v="de"/>
    <tag k="addr:housenumber" v="4"/>
    <tag k="addr:postcode" v="69120"/>
    <tag k="addr:street" v="brückenstraße"/>
    <tag k="name" v="buchhandlung schmitt &amp; hahn"/>
    <tag k="shop" v="books"/>
    <tag k="wheelchair" v="no"/>

well - found interesting thing: how import xml file mysql database table using xml_load(); function - stack overflow

have xml file looks :

code: select all

    <?xml version="1.0" encoding="utf-8"?>

<resultset statement="your sql statements generate 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 trying import in mysql using sql statement :

use databasename;

code: select all

load xml local infile '/pathtofile/file.xml' table my_tablename;


the table my_tablename has following fields :

code: select all

id (auto increment id)
personal_number(varchar)
firstname(varchar)
lastname(varchar)
email(varchar)
start_time(varchar)
end_time(varchar)
employee_category(varchar)



i error : error code: 1136 column count doesn't match value count @ row 1

i using mysql 5.1.56

i assume error occurs because database table has field id, not present in xml file. how possible import xml file using mysql queries of built in functions such skips id column during import , relies on auto increment function id column? there smarter way of handling xml file imports im mysql? maybe there better statement allows specify column mapping?

well guess can specify fields this:

code: select all

load xml local infile '/pathtofile/file.xml'
into table my_tablename(personal_number, firstname, ...);


since id auto increment, can specify id=null as,

code: select all

load xml local infile '/pathtofile/file.xml' table my_tablename set id=null;



on other hand - can xml-simple

we can run perl text-mangling xml-simple-2.20/lib/xml/simple.pm

i ve found solution xml-simple: here's example of little script parse our xml:

#!/usr/bin/perl

code: select all

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 $xmlfile: $!\n";
};
die $@ if($@);
print dumper $ref;


which, if passed our xml file goes well

what say?!


note: see source (s)= http://download.geofabrik.de/europe/germany.html ranging form 10 mb (bremen) 390 mb (nordrhein westfalen) osm.pbf-files not big; question: method best - appropiate? - store results in mysql-db or have big calc-sheets (with csv-data)

- straightforward files that
- no backimport of data osm .csv file