title: Perl: XML to MySQL with LibXML
body: <p>I'm pretty new to Perl and i'm writing a perl script to practice and i want to parse info from XML file to MySQL DB , but i'm stuck and i cannot find a way to import the data to MySQL.</p>
  
  <p>Here is my Perl code:</p>
  
  <pre><code>#!/usr/local/bin/perl
  use strict;
  use warnings;
  use diagnostics;
  use XML::LibXML;
  use DBI;
  my $filename = 'test.xml';
  my $dom = XML::LibXML-&gt;load_xml(location =&gt; $filename);
  my $sport_id;
  my $sport_name;
  my $competition_id;
  my $competition_name;
  my $game_id;
  my $game_start;
  my $game_name;
  my @values;
  my $dbh = DBI-&gt;connect("dbi:mysql:parser:127.0.0.1", "root", "123qwe", { RaiseError =&gt; 1}) or die $DBI::errstr;
  my $query = 'INSERT INTO sports (sport_id,sport_name,competition_id,competition_name,game_id,game_start,game_name) VALUES (?,?,?,?,?,?,?)';
  my $sth = $dbh-&gt;prepare($query) or die "Prepare failed: " . $dbh-&gt;errstr();
  
  foreach my $test ($dom-&gt;findnodes('//Sport')) {
      print "\n";
      $sport_id = $test-&gt;findvalue('./ID');
      $sport_name = $test-&gt;findvalue('./Name');
      $competition_id = $test-&gt;findvalue('./Competitions/Competition/ID');
      $competition_name = $test-&gt;findvalue('./Competitions/Competition/Name');
      $game_id = $test-&gt;findvalue('./Competitions/Competition/Games/ID');
      $game_start = $test-&gt;findvalue('./Competitions/Competition/Games/Start');
      $game_name = $test-&gt;findvalue('./Competitions/Competition/Games/Name');
      #print "Sport ID: $sport_id\n";
      #print "Sport Name: $sport_name\n";
      #print "Competition ID: $competition_id\n";
      #print "Competition Name: $competition_name\n";
      #print "Game ID: $game_id\n";
      #print "Game Start: $game_start\n";
      #print "Game Name: $game_name\n";
      #print "\n";
      push @values, $sport_id,$sport_name,$competition_id,$competition_name,$game_id,$game_start,$game_name;
      $sth-&gt;execute(@values) or die $dbh-&gt;errstr;
  }
  </code></pre>
  
  <p>My XML:</p>
  
  <pre><code>&lt;Sports&gt;
  &lt;Sport&gt;
  &lt;ID&gt;1369527874&lt;/ID&gt;
  &lt;Name&gt;Virtual Football&lt;/Name&gt;
  &lt;Competitions&gt;
  &lt;Competition&gt;
  &lt;ID&gt;1374380502&lt;/ID&gt;
  &lt;Name&gt;Virtual Football. World - G.Devs Stadium&lt;/Name&gt;
  &lt;Games&gt;
  &lt;ID&gt;1974885309&lt;/ID&gt;
  &lt;Start&gt;2016-05-11 12:21:00&lt;/Start&gt;
  &lt;Name&gt;New England Militia - St. Louis Racers&lt;/Name&gt;
  &lt;ID&gt;1974892839&lt;/ID&gt;
  &lt;Start&gt;2016-05-11 12:27:00&lt;/Start&gt;
  &lt;Name&gt;Las Vegas Rollers - Salt Lake Wrath&lt;/Name&gt;
  &lt;/Games&gt;
  &lt;/Competition&gt;
  &lt;/Competitions&gt;
  &lt;/Sport&gt;
  &lt;Sport&gt;
  &lt;ID&gt;882&lt;/ID&gt;
  &lt;Name&gt;Darts&lt;/Name&gt;
  &lt;Competitions&gt;
  &lt;Competition&gt;
  &lt;ID&gt;1834852369&lt;/ID&gt;
  &lt;Name&gt;Darts. World - PDC European Tour Outright&lt;/Name&gt;
  &lt;Games&gt;
  &lt;ID&gt;1895020486&lt;/ID&gt;
  &lt;Start&gt;2016-05-15 23:00:00&lt;/Start&gt;
  &lt;Name&gt;PDC European Tour. Outright&lt;/Name&gt;
  &lt;/Games&gt;
  &lt;/Competition&gt;
  &lt;/Competitions&gt;
  &lt;/Sport&gt;
  &lt;/Sports&gt;
  </code></pre>
  
  <p>MySQL structure:</p>
  
  <pre><code>+------------------+--------------+------+-----+---------+----------------+
  | Field            | Type         | Null | Key | Default | Extra          |
  +------------------+--------------+------+-----+---------+----------------+
  | id               | int(6)       | NO   | PRI | NULL    | auto_increment |
  | sport_id         | varchar(255) | YES  |     | NULL    |                |
  | sport_name       | varchar(255) | YES  |     | NULL    |                |
  | competition_id   | varchar(255) | YES  |     | NULL    |                |
  | competition_name | varchar(255) | YES  |     | NULL    |                |
  | game_id          | varchar(255) | YES  |     | NULL    |                |
  | game_start       | varchar(255) | YES  |     | NULL    |                |
  | game_name        | varchar(255) | YES  |     | NULL    |                |
  +------------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>If i remove the comment from the print lines the output will be as follow:</p>
  
  <pre><code>Sport ID: 1369527874
  Sport Name: Virtual Football
  Competition ID: 1374380502
  Competition Name: Virtual Football. World - G.Devs Stadium
  Game ID: 19748853091974892839
  Game Start: 2016-05-11 12:21:002016-05-11 12:27:00
  Game Name: New England Militia - St. Louis RacersLas Vegas Rollers - Salt Lake Wrath
  
  
  Sport ID: 882
  Sport Name: Darts
  Competition ID: 1834852369
  Competition Name: Darts. World - PDC European Tour Outright
  Game ID: 1895020486
  Game Start: 2016-05-15 23:00:00
  Game Name: PDC European Tour. Outright
  </code></pre>
  
  <p>As you can see , the main problem is that i have multiple games and i cant seem to find a way to split them so i can import them to mysql.</p>