Perl Object to XML Using XML::Simple

Question

I'm attempting to use the XML::Simple CPAN module to convert output from our database into a simple XML structure. The problem is that the output being returned isn't what I was hoping for no matter what options I attempt to pass to XML::Simple.

The database table we're trying to output is just a bunch of items with the definition:

CREATE TABLE `items` (
  `id` int(8) NOT NULL,
  `name` varchar(40) NOT NULL,
  `manufacturer` varchar(40) NOT NULL,
  `added` datetime NOT NULL,
  PRIMARY KEY  (`id`)
);

Here is the Perl code we're currently using:

my $SQL = qq| select * from items |;
my $sth = main::DatabaseQuery($SQL);

my $items;
my $count = 0;
while(my $item = $sth->fetchrow_hashref()) {
    $items->[$count] = $item;
    $count++;
}

require XML::Simple;
my $xs = XML::Simple->new(ForceArray => 1, KeepRoot => 1);
my $xml = $xs->XMLout($ref);

This outputs the following XML:

<anon id="10000" name="Item 1" manufacturer="Manufacturer 1" added="2009-01-01" /> 
<anon id="10001" name="Item 2" manufacturer="Manufacturer 2" added="2009-01-01" /> 
<anon id="10002" name="Item 3" manufacturer="Manufacturer 3" added="2009-01-01" /> 

I instead want it output as follows:

<items>
    <item>
        <id>10000</id> 
        <name>Item 1</name> 
        <manufacturer>Manufacturer 1</manufacturer> 
        <added>2009-01-01</added> 
    </item>
    <item>
        <id>10001</id> 
        <name>Item 2</name> 
        <manufacturer>Manufacturer 2</manufacturer> 
        <added>2009-01-01</added> 
    </item>
    <item>
        <id>10002</id> 
        <name>Item 3</name> 
        <manufacturer>Manufacturer 3</manufacturer> 
        <added>2009-01-01</added> 
    </item>
</items>

I'm sure there is some minor thing I'm overlooking so please let me know how I can change our current implementation to produce our desired XML output. Thanks in advance!

Answer 1

Try:

my $SQL = qq| select * from items |;
my $sth = main::DatabaseQuery($SQL);

my $items = [];
while(my $item = $sth->fetchrow_hashref()) {
    push @$items, $item;
}

use XML::Simple;
my $xs = XML::Simple->new(ForceArray => 1, KeepRoot => 1, NoAttr => 1);
my $xml = $xs->XMLout( { items => { item => $items } } );

Note that I changed it to use push instead of counting and assigning to the last array index and also initialized items so you don't get <item></item> when there are no data rows.