#perl
use strict;
use DBI;
use XML::LibXML;
use Data::Dump 'pp';
my %DB=();

my $dbh = get_dbh(); # connect
setup($dbh); # set up database
initDB($dbh,\%DB); # fill hash

my $parser = XML::LibXML->new();
my @xmlfiles = ('test_1.xml');
for my $xmlfile (@xmlfiles){
  print "Parsing $xmlfile\n";
  my $doc = readXmlFile($xmlfile);
  writeXmlFile($doc,'new_'.$xmlfile);
}

# parse XML
sub readXmlFile { 
  my $file = shift;
  my $doc = $parser->parse_file($file);
  my @nodes_mt = $doc->findnodes ('/mdc/md/mi/mt');
  for my $node_mt (@nodes_mt){
    my $elemNameID = $node_mt->getAttribute("p");
  
    # update database if not exists
    updateDB($dbh,\%DB,$elemNameID) 
        unless exists $DB{$elemNameID};
    
    # translate name to newid    
    my $newID = $DB{$elemNameID};
  
    my @nodes_r = $doc->findnodes ('/mdc/md/mi/r');
    for my $node_r (@nodes_r){
      my $elemValueID = $node_r->getAttribute("p");
      if ($elemNameID eq $elemValueID){
        print join " ",$node_mt->textContent,$node_r->textContent,$new
+ID,"\n";
        # update with newid
        $node_mt->setAttribute(p => $newID);
        $node_r->setAttribute(p => $newID);
      }
    }
  }
  return $doc;
}

sub writeXmlFile {
  my ($doc,$outfile) = @_;
  open my $fh,'>',$outfile or die "Could not open $outfile : $!";
  print $fh $doc->toString;
  close $fh;
}

pp \%DB;

# update db
sub updateDB {
  my ($dbh,$db,$name) = @_;
  
  my $sql = 'INSERT INTO XMLelementen 
             VALUES (NULL,?)';
  $dbh->do($sql,undef,$name) if ($name);
  
  my $sql = 'SELECT id,name FROM XMLelementen
             WHERE name=?';
  my ($id,$name) = $dbh->selectrow_array($sql,undef,$name); 
  $db->{$name} = $id;
}

# initialize db hash from database
sub initDB {
  my ($dbh,$db) = @_;
  my $sql = 'SELECT id,name FROM XMLelementen';
  my $sth = $dbh->prepare($sql);
  $sth->execute();
  while ( my ($id,$name) = $sth->fetchrow_array ){ 
    $db->{$name} = $id;
  }
}

# connect to database
sub get_dbh{
  my $database = "test"; my $user = "user"; my $pw = "password";
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, 
  { RaiseError=>1, AutoCommit=>1 } );
  return $dbh;
};

# create database
sub setup {
  $dbh = shift;
  my $sql = "CREATE TABLE xmlelementen (
    id    integer NOT NULL AUTO_INCREMENT,
    name  char(255) NOT NULL,
    PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
  #$dbh->do('DROP TABLE XMLelementen');
  $dbh->do($sql);
  
  my $sql = 'INSERT IGNORE INTO XMLelementen 
             VALUES (NULL,?)';
  $dbh->do($sql,undef,'X');
  $dbh->do($sql,undef,'Y');
  $dbh->do($sql,undef,'Z');
}

__DATA__
<?xml version="1.0"?>
<mdc>
  <md>
    <mi>
      <mt p="A1">mt0</mt> 
      <mt p="C1">mt1</mt> 
      <mt p="D1">mt2</mt> 
      <r p="A1">p0</r> 
      <r p="C1">p1</r> 
      <r p="D1">p2</r>  
     </mi>
  </md>
</mdc>
[download]