transforming data xml to csv: doable?

i am pretty new to openstreetmap

update see below!

we can run Perl for text-mangling XML-Simple-2.20/lib/XML/Simple.pm here's an example of a little script to parse our XML:

#!/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;

which, if passed our XML file goes very well

on http://overpass-turbo.eu/ i have runned the following code

i run the following code in opverpass-api - see here http://overpass-turbo.eu/

<query type="node">
  <has-kv k="place" v="city"/>
  <has-kv k="name" v="Peking"/>
</query>
<query type="node">
  <around radius="1000"/>
    <has-kv k="shop"/>
</query>
<print/>

Export of the data to the following formats

to GeoJSON
to GPX
to KML

get the data from

see the Overpass API-explanations: i have the options to loat them to JOSM laden (only for requests, that give back valid OSM-XML with Metadata) GeoJSON to save it as gist

note - i did not install the overpass-api on my opensuse 13.1 yet. but i am willing to do so.

as for now - running the above mentioned code in the oerpass-api - here. http://overpass-turbo.eu/

how to treat it to get it exported as csv-formated hope i was able to provide all the necessary things for a clear and concise question.

all i want is to transforme the xml-data to csv - in order to get a excel or calc based output.

<?xml version="1.0" encoding="UTF-8"?>
<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="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"/>

look forward to hear from you

update 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;