Re: sql output from dbi as an xml file

[cp]

[ email to list and user directly]

Here is a short example using XML::Simple available from CPAN. Forgive the
inelegant text wrap. This example takes a table of usernames and user
numbers, and emits XML.

#!/usr/local/bin/perl -w

use strict; 
use XML::Simple; 
use DBI; 
use Data::Dumper; 

my $dsn = 'dbi:mysql:clients;host=localhost';
my $user = 'mmarti';
my $pass = '10072000';

my $dbh = DBI->connect($dsn, $user, $pass, {RaiseError=>1});
die "Connect Failed\n" unless $dbh;

my $sth = $dbh->prepare(qq(
                SELECT  agentID,
                        concat(lastname, ', ', firstname) as username
                FROM users ) );
                
$sth->execute(); 
my $keyattr = 'agentID';
my $hashref = $sth->fetchall_hashref($keyattr);

# I'm confused. Do I need to call finish here since I'm not looping?
# or does DBI do that automatically on fetchall_hashref?

my $ref;
my $supress_at = 1;
my $element_name = 'user';
$ref->{$element_name} = $hashref;

my $xml = XMLout($ref, xmldecl=>1, keyattr=>{$element_name=>$keyattr},
                 rootname=>'root', noattr=>$supress_at);

# modify to suit
print Dumper( $ref );
print $xml; 


__END__
 print Dumper( $ref ) produces:
$VAR1 = {
          'user' => {
                      '70' => {
                                'agentID' => '70',
                                'username' => 'Cote, Marie'
                              },
                      '38' => {
                                'agentID' => '38',
                                'username' => 'Ross, Jennie'
                              }
                       }
        }
With $suppress_at set to undef, print $xml produces:

<?xml version='1.0' standalone='yes'?>
<root>
  <user agentID="70" username="Cote, Marie" />
  <user agentID="38" username="Ross, Jennie" />
</root>

With $supress_at = 1 print $xml produces:
<?xml version='1.0' standalone='yes'?>
<root>
  <user>
    <agentID>70</agentID>
    <username>Cote, Marie</username>
  </user>
  <user>
    <agentID>38</agentID>
    <username>Ross, Jennie</username>
  </user>
</root>

cp
-- 
http://www.pryce.net

> From: "Kairam, Raj" <[EMAIL PROTECTED]>
> Date: Wed, 31 Jul 2002 14:54:59 -0400
> To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
> Cc: "Kairam, Raj" <[EMAIL PROTECTED]>
> Subject: sql output from dbi as an xml file
> 
> 
> How can I ouput sql query results as xml ?. Is there something like xsql to
> use on the command line or put in a perl program that currently connects to
> database,does the query and outputs results as a flat text file.
> Any advice will be appreciated.
> 
> My environment:
> HP-UX 11.0
> perl 5.005_03
> Apache 1.3.12
> Oracle 8.1.6
> DBI 1.14
> DBD-Oracle-1.06