rycher has asked for the wisdom of the Perl Monks concerning the following question:

There is a MySQL XML dump that contains data in the following format: 
<row>
<field name='column1'>Homer</field>
<field name='column2'>Homer Simpson</field>
<field name='column3'>Nuclear Systems Operation</field>
</row>

[download]

I would like to key in on the 'field name' or convert the above to the following format: 
<row>
<column1>Homer</column1>
<column2>Homer Simpson</column2>
<column3>Nuclear Systems Operation</column3>
</row>

[download]

This is the code I currently have. It will work for the second 'converted' version of the XML output.: 
    my $xml = new XML::Simple (KeyAttr=>'row');
    my $data = $xml->XMLin("Employee.xml");
    foreach my $e (@{$data->{row}})
    {
        print "cn: ", $e->{column1}, "\n";
        print "sn: ", $e->{column2}, "\n"; 
        print "Department: ", $e->{column3}, "\n"; 
        print "\n";
     }

[download]


Ideally, I would like to skip the conversion process and just be able to key in on the 'field name' entry. If this is not possible, How would I first convert the file replace 'field name='column1' with just 'column1' and '/field' with '/column1'? I've tried cheating with a system command & perl one-liner within my script to do a search and replace. It didn't work as expected and was pretty fugly.

Replies are listed 'Best First'.
Re: Change MySQL 5.0 XML output to 4.0 output
by Anonymous Monk on Apr 26, 2009 at 16:07 UTC
    No need to convert 
    #!/usr/bin/perl -- 

use strict;
use warnings;


use XML::Simple;

my $xml =<<'__XML__';
<row>
<field name='column1'>Homer</field>
<field name='column2'>Homer Simpson</field>
<field name='column3'>Nuclear Systems Operation</field>
</row>
__XML__

$xml =~ s/^\s+//s;
$xml =~ s/\s+\z//s;

my $row = XMLin($xml,ForceContent=>1, ForceArray=>1);

print Data::Dumper->new([$row])->Indent(1)->Dump;use Data::Dumper;


for my $key( keys %{$row->{field}} ){
    print "$key => ", $row->{field}{$key}{content},"\n";
}


__END__

$VAR1 = {
  'field' => {
    'column3' => {
      'content' => 'Nuclear Systems Operation'
    },
    'column2' => {
      'content' => 'Homer Simpson'
    },
    'column1' => {
      'content' => 'Homer'
    }
  }
};
column3 => Nuclear Systems Operation
column2 => Homer Simpson
column1 => Homer

    [download]
[reply]
[d/l]
      Thanks!
      That works nicely for one entry... how would I do multiple entries?: 
      <?xml version='1.0'?>
<table_data>
<row>
<field name="column1">Homer</field>
<field name="column2">Homer Simpson</field>
<field name="column3">Nuclear Systems Operation</field>
</row>
<row>
<field name="column1">Marge</field>
<field name="column2">Marge Simpson</field>
<field name="column3">Homers caretaker</field>
</row>
</table_data>

      [download]
      Thank you for all of the assistance.
[reply]
[d/l]
        By programming :) 
        #!/usr/bin/perl -- 

use strict;
use warnings;


use XML::Simple;

my $xml =<<'__XML__';
<?xml version='1.0'?>
<table_data>
<row>
<field name="column1">Homer</field>
<field name="column2">Homer Simpson</field>
<field name="column3">Nuclear Systems Operation</field>
</row>
<row>
<field name="column1">Marge</field>
<field name="column2">Marge Simpson</field>
<field name="column3">Homers caretaker</field>
</row>
</table_data>

__XML__

$xml =~ s/^\s+//s;
$xml =~ s/\s+\z//s;

my $rows = XMLin($xml,ForceContent=>1, ForceArray=>1);

print Data::Dumper->new([$rows])->Indent(1)->Dump;use Data::Dumper;

for my $row( @{ $rows->{row} }){
    for my $key( keys %{$row->{field}} ){
        print "$key => ", $row->{field}{$key}{content},"\n";
    }
}
__END__
$VAR1 = {
  'row' => [
    {
      'field' => {
        'column3' => {
          'content' => 'Nuclear Systems Operation'
        },
        'column2' => {
          'content' => 'Homer Simpson'
        },
        'column1' => {
          'content' => 'Homer'
        }
      }
    },
    {
      'field' => {
        'column3' => {
          'content' => 'Homers caretaker'
        },
        'column2' => {
          'content' => 'Marge Simpson'
        },
        'column1' => {
          'content' => 'Marge'
        }
      }
    }
  ]
};
column3 => Nuclear Systems Operation
column2 => Homer Simpson
column1 => Homer
column3 => Homers caretaker
column2 => Marge Simpson
column1 => Marge

        [download]
        references quick reference
[reply]
[d/l]

Back to Seekers of Perl Wisdom