use strict;
use warnings;

use DBI;
use XML::LibXML 2;
use XML::LibXML::PrettyPrint qw(print_xml);

my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:","","");

# Create demo data...
$dbh->do("CREATE TABLE sku (item text, loc text)");
$dbh->do("INSERT INTO sku VALUES ('011-5000', 'DLSDC')");
$dbh->do("INSERT INTO sku VALUES ('011-5100', 'FRMNT')");

my $xml  = XML::LibXML::Document->new();
my $root = XML::LibXML::Element->new('results');
$xml->setDocumentElement($root);

my $metadata = $root->addNewChild(undef, 'metadata');
my $data     = $root->addNewChild(undef, 'data');

my $sth = $dbh->prepare("SELECT * FROM sku");
my $meta_done = 0;  # false

$sth->execute;
while (my @row = $sth->fetchrow_array)
{
    unless ($meta_done)
    {
        my $alias_num = 0;
        for my $col (@{$sth->{NAME_uc}})
        {
            my $field = $metadata->addNewChild(undef, 'field');
            %$field = (
                name  => $col,
                alias => "a".++$alias_num,
            );
        }
        $meta_done = 1;
    }
    
    my $rec = $data->addNewChild(undef, 'rec');
    for my $i (1 .. @row)
    {
        $rec->addNewChild(undef, "a$i")->appendText($row[$i-1]);
    }
}


my $pretty = XML::LibXML::PrettyPrint->new(
    element => { compact => sub { ($_[0]->localname||'') =~ /^a[0-9]+$
+/ } },
);
$pretty->pretty_print($xml);
print $xml->toString;
[download]