Alright, after taking everyones advice, here is what I came up with and works a bit better.

First extact the XML document from the CLOB column in Oracle.

#****VARIABLES****#

my $username = 'system';
my $password = '';
my $database = "dbi:Oracle:";
my $oradbh;
my $xmlSQL;
my $sqlXML;
my $count=0;
my $xmldatafile = "E:\\<company>\\PERLFiles\\trash\\xmldata.xml";
my $datadump = "E:\\<company>\\PERLFiles\\trash\\extract.txt";

#****END VARIABLES****#
#****PROGRAM****#

$oradbh = DBI->connect($database,$username,$password,
                       {RaiseError => 1,
                        PrintError => 0})
        or die ($DBI::errstr, "\n");

#Sets the Long Read Length to accomendate the XML in the RFI_XML colum
+n
$oradbh -> {LongReadLen} = 30000*256;

#SQL setup to extract the XML data from the 8i database
$xmlSQL = qq(select rfi_xml from webrater_owner.quote where rownum < 1
+2);
$sqlXML = $oradbh->prepare($xmlSQL);
$sqlXML -> execute();
my ($xml_sql_data);
$sqlXML -> bind_columns(undef, \$xml_sql_data);

#Write out all the XML data from RFI_XML to a text/xml file

while($sqlXML -> fetch())
{
    open(FILE,"> $xmldatafile".$count) or die "Unable to open $xmldata
+file : $!\n";
    
    foreach($sqlXML)
    {
        print FILE $xml_sql_data;
    }
    
    close(FILE) or warn "Unable to close $xmldatafile: $!\n";
    print "writing file: ".$xmldatafile.$count."\n";
    $count++;
}

$sqlXML -> finish(); #Finish the SQL command
$oradbh -> disconnect; #Disconnect from the database

#****END PROGRAM****#
[download]

#C:\PERL\bin\perl.exe -w

#****PACKAGES****#

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

my $xmldatafile = "E:\\<company>\\PERLFiles\\trash\\xmldata.xml";
my $xmldatafile2 = "E:\\<company>\\PERLFiles\\trash\\dumped.txt";
my $datadump = "E:\\<company>\\PERLFiles\\trash\\simpleextract.txt";
my $xml;
my $data;
my $dr; 
my $vh;
my $newdata;
my $count=0;
my $filecount=0;
my $drivercount=0;
my $vehiclecount=0;
my @policydata;
my @driverdata;
my @vehicledata;

open(XMLFILE,"> $xmldatafile2") or die "Unable to open file: $!\n";
print XMLFILE "IdNum|Quote|EffectiveDate|LastName|FirstName|Address|Ci
+ty|State|Zip|LastName|FirstName|Sex|MaritalStatus|DOB|LicenseNumber|L
+icenseState|Status|DriverNum|Year|Make|Model|VIN|VehicleNum\n";
while($count <= 12)
{
    $xml = new XML::Simple (KeyAttr=>[],forcearray=>['driver','vehicle
+']);
    
    $data = $xml->XMLin($xmldatafile.$count);

    print "reading file: $xmldatafile$count\n";
    
    open(FILE,"> $datadump");
    print FILE Dumper($data);
    close(FILE);

        
    $drivercount=0;
    $vehiclecount=0;
    
    $policydata[0] = $data->{WebId};
    $policydata[1] = $data->{effectiveDate};
    $policydata[2] = $data->{LastName};
    $policydata[3] = $data->{FirstName};
    $policydata[4] = $data->{applicantAddress1};
    $policydata[5] = $data->{applicantCity};
    $policydata[6] = $data->{applicantState};
    $policydata[7] = $data->{applicantZip};
    
    foreach $dr (@{$data->{driver}})
    {
        $driverdata[0] = $dr->{lastName};
        $driverdata[1] = $dr->{firstName};
        $driverdata[2] = $dr->{sex};
        $driverdata[3] = $dr->{maritalStatus};
        $driverdata[4] = $dr->{'Birth-Date'};
        $driverdata[5] = $dr->{'License-No'};
        $driverdata[6] = $dr->{'License-State'};
        $driverdata[7] = $dr->{RATESTATUS};
        $driverdata[8] = $drivercount;
        $drivercount++;
    }

    foreach $vh (@{$data->{vehicle}})
    {
        $vehicledata[0] = $vh->{year};
        $vehicledata[1] = $vh->{make};
        $vehicledata[2] = $vh->{model};
        $vehicledata[3] = $vh->{vin};
        $vehicledata[4] = $vehiclecount;
        $vehiclecount++;
    }

    foreach $newdata ($filecount, @policydata, @driverdata, @vehicleda
+ta)
    {
        print XMLFILE $newdata.'|';
    }
    
    print XMLFILE "\n";

    $filecount++;
    $count++;    

} #End While Loop

close(XMLFILE);
[download]

Its not exactly perfect, but it gets the job done

Thanks,

Curtisbl

You don't need to assign to the individual elements of the arrays, set the whole array at once. Instead of

$vehicledata[0] = $vh->{year};
$vehicledata[1] = $vh->{make};
$vehicledata[2] = $vh->{model};
$vehicledata[3] = $vh->{vin};
$vehicledata[4] = $vehiclecount;
$vehiclecount++;
[download]

use just

@vehicledata = ( $vh->{year}, $vh->{make}, $vh->{model}, $vh->{vin}, $
+vehiclecount++);
[download]

or even

@vehicledata = ( @{$vh}{year, make, model, vin}, $vehiclecount++);
[download]

Hopefully it'll make the problem more apparent. You dutifully assign to @driverdata and @vehicledata in loops, but do the printing outside the loop. So if there are several drivers or vehicles in the XML you only print the last one. I'm not sure what do you want to do if there are both several drivers and vehicles though.

P.S.: The stuff you are printing is not XML so I don't think the filehandle should be named XMLFILE.

Jenda


Support Denmark! Defend the free world!