#****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 column
$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 < 12);
$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 $xmldatafile : $!\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****#

##</code><code>##

#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|City|State|Zip|LastName|FirstName|Sex|MaritalStatus|DOB|LicenseNumber|LicenseState|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, @vehicledata)
    {
        print XMLFILE $newdata.'|';
    }
    
    print XMLFILE "\n";

    $filecount++;
    $count++;    

} #End While Loop

close(XMLFILE);