#!perl
use strict;
use DBD::Oracle;
use XML::Writer;
use Data::Dump 'pp';

my $outfile = 'output.xml';
my $RLTP_ID = 10;

# connect to database
my $dbh = dbh();

# RLTP
my $sql = 'SELECT rltp_id,rltp_name from A_RLTP_MNGR
           WHERE rltp_id=?';
my $sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
my $RLTP = $sth->fetchall_hashref(1);

# PRODUCT
$sql = 'SELECT prod_id,prod_name from A_PRODUCT
        WHERE rltp_id=?';
$sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
my $PRODUCT = $sth->fetchall_hashref(1);

# CUSTOMER
$sql = 'SELECT cust_id,cust_name from A_CUST
        WHERE rltp_id=?';
$sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
my $CUST = $sth->fetchall_hashref(1);

# ACCOUNT
$sql = 'SELECT acc_id,acc_name,acc_balance from A_ACCOUNT
        WHERE rltp_id=?';
$sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
my $ACCOUNT = $sth->fetchall_hashref(1);

# TRANS
my %TRANS=();
$sql = 'SELECT prod_id,cust_id,acc_id,txn_id,txn_amt from A_TRANSACTIO
+N
        WHERE rltp_id=?';
$sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
while (my @f = $sth->fetchrow_array){
  $TRANS{$f[0]}{$f[1]}{$f[2]}{$f[3]}{'amt'}=$f[4];
}
$dbh->disconnect;

# CREATE XML
my $t0 = time();
my $output = IO::File->new(">$outfile");
my $w = XML::Writer->new(
  OUTPUT => $output,
  DATA_MODE => 1,
  DATA_INDENT=>2 );
$w->startTag('transactiondetails');   
$w->dataElement('rltp_id'   => $RLTP_ID);
$w->dataElement('rltp_name' => $RLTP->{$RLTP_ID}{'RLTP_NAME'});

for my $prod (sort keys %TRANS){

  $w->startTag('product');   
  $w->dataElement('product_id'   => $prod);
  $w->dataElement('product_name' => $PRODUCT->{$prod}{'PROD_NAME'});
  
  for my $cust (sort keys %{$TRANS{$prod}}){

    $w->startTag('customer');   
    $w->dataElement('cust_id'   => $cust);
    $w->dataElement('cust_name' => $CUST->{$cust}{'CUST_NAME'});

    for my $acc (sort keys %{$TRANS{$prod}{$cust}}){
    
      $w->startTag('account');  
      $w->dataElement('acc_id'      => $acc);
      $w->dataElement('acc_name'    => $ACCOUNT->{$acc}{'ACC_NAME'});
      $w->dataElement('acc_balance' => $ACCOUNT->{$acc}{'ACC_BALANCE'}
+);
      #$w->dataElement('acc_type'    => 0);
      $w->endTag('account');    
    }  
    $w->endTag('customer');
  }
  $w->endTag('product');   
}

$w->endTag('transactiondetails');   
$w->end();
$output->close();
my $dur  = time() - $t0;
print "XML created as $outfile in $dur seconds\n";

# connect
sub dbh {
  my $host = "localhost";
  my $sid  = 'xe';
  my $user = 'user';
  my $pwd  = 'password';
  my $dsn = "dbi:Oracle:host=$host;sid=$sid";
  my $dbh = DBI->connect($dsn, $user, $pwd, 
            { AutoCommit => 0,
            })  or die "$!";
  return $dbh
}
[download]