use 5.008;
use strict;
use warnings;

use DBI();
use XML::Smart;
use Data::Dumper;   #for debugging purposes only

BEGIN { $| = 1 };

my $location = 'xxxxxx';

my $XML = XML::Smart->new($location.'CategoriesList.xml')
      or die("Unable to parse CategoriesList.xml: $!");;
  $XML = $XML->cut_root();
  $XML = $XML->cut_root();

  $dbh->do("DROP TABLE IF EXISTS ice_categories");
  $dbh->do("CREATE TABLE ice_categories (
      category_id int(11) not null,
      parent_cat_id int(11) not null,
      category_name varchar(100) not null default '',
      category_description varchar(100) not null default '',
      category_image varchar(100) not null default '',
      category_thumb varchar(100) not null default '',
      KEY (category_id), KEY (parent_cat_id))
      CHARACTER SET utf8 COLLATE utf8_unicode_ci;");

  my @Categories = @{$XML->{CategoriesList}{Category}};

  my $c_categories = 0;
  foreach my $category (@Categories) {
    my $cat_name = ucwords($category->{Name}('langid','eq','1')->{Value});
    #print $category->{ID} . " => " . $cat_name . "\n";
    my $cat_desc = $category->{Description}('langid','eq','1')->{Value};
  $dbh->do("INSERT ice_categories (category_id, parent_cat_id, category_name, category_description, category_image, category_thumb) 
      VALUES (".$category->{ID}.", ".$cat_parent.", ".$dbh->quote($cat_name).", ".$dbh->quote($cat_desc).", ".$dbh->quote($category->{LowPic}).", ".$dbh->quote($category->{ThumbPic}).")");
    $c_categories++;
  }

  print "$c_categories categories imported.\n";
}

1;


##</code><code>##

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ICECAT-interface SYSTEM "http://data.icecat.biz/dtd/ICECAT-interface_response.dtd">
 <ICECAT-interface>
   <Response Date="Sat Apr 26 14:46:53 2014" ID="13219513" Request_ID="1398516412" Status="1">
    <CategoriesList>
      <Category ID="127" LowPic="http://images.icecat.biz/img/low_pic/127-563.jpg" Score="9725" Searchable="0" ThumbPic="http://images.icecat.biz/thumbs/CAT127.jpg" UNCATID="43171520" Visible="0">
        <Description ID="548838" Value="Device or stand where you can rest your mobile or fixed telephone." langid="1"/>
        <Description ID="8310" Value="" langid="2"/>
        <Keywords ID="3274" Value="" langid="1"/>
        <Keywords ID="3275" Value="" langid="2"/>
        <Keywords ID="3276" Value="" langid="3"/>
        <Keywords ID="3277" Value="" langid="4"/>
        <Keywords ID="3278" Value="" langid="5"/>
        <Name ID="255" Value="telephone rests" langid="1"/>
        <Name ID="471173" Value="telefoon steunen" langid="2"/>
        <Name ID="343915" Value="autres téléphones" langid="3"/>
        
       <ParentCategory ID="242">
        <Names>
          <Name ID="485" langid="1">networking</Name>
          <Name ID="471244" langid="2">netwerken</Name>
        </Names>
       </ParentCategory>
      </Category> </CategoriesList>