Use XML::Twig or another XML parser. Trying to roll your own is just masochism.

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]


Then process each file of XML into one file with multiple rows.


#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!

Now, what am I trying to do. I have an XML document that I have to parse. I can parse everything, except for multiple drivers and vehicles. Everything I run this I only get one driver when I have two drivers. The beginning tag is <driver id=> (what I'm searching off of). The same with vehicles.

I knew nothing about XML till the last day (now I know next to nothing). Then I had to parse some XML and I had heard about XML::Simple judging that if it were really simple, then it would have been the right tool for me. Indeed, it turned out to be. It just slurped the XML (file or string) into a handy, familiar Perl data structure. For multiple tags I get an arrayref, and that's it. So all in all if you don't need the full, lower lever, parsing power of XML::Parser, you may try it as well. OTOH the code you posted at first sight seems to verbose for me to want to delve into it in enough detail (not to mention the fact that at first sight I see many things I don't like), and although somebody will probably be less lazy than I am, I bet many will be scared about it too, so I recommend preparing a minimal example exhibiting the problem yourself.