Hi Monks!

I am parsing a big xml document that has to go into a database; I have the parsing portions of it working fine, but my question now is on the INSERTING of these values into the database.
I am thinking in do the inserts as I am going through the xml to avoid having to use "PUSH" for each variable, since this xml is about 300 lines long. Is there a way of doing this? Or any better way to do this?
Can someone help me on that? I have some code that would be helpful on the bottom of this if it could be implemented to work with what I have, I assume that it would be fine, any thoughts?
Thank you!


#!/perl/bin/perl

use strict;
use CGI qw/:standard/;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use XML::XPath;
use XML::XPath::XMLParser;

print header();

my ($replication,$part_no,$add_sample_ref,$fname,$carr,$carr_nr,$overl
+ay,$billa,$billb,$percent,$rref);
my (@replication,@part_no,@add_sample_ref,@fname,@carr,@carr_nr,@overl
+ay,@billa,@billb,@percent,@rref);



my $xp = XML::XPath->new(filename => 'template.xml');

foreach my $row ($xp->findnodes('/CITY/replication')) {

 # extracting from the XML 
 $replication = $row->find('//@REFA')->string_value;
 $part_no = $row->find('Part_No')->string_value;

        # Start
        my $nodeset = $row->find('AdCar');
        foreach my $node ( $nodeset->get_nodelist ) {
        
           $add_sample_ref = $node->find( '@Ref' )->string_value;
           $fname = $node->find( 'FName')->string_value;
               
               push @add_sample_ref,$add_sample_ref;
               #push @
               
        } #End

        # Start
        my $node_carr = $row->find('AbCarr');

        foreach my $node ( $node_carr->get_nodelist ) {

               $carr = $node->find( '@RefD' )->string_value;
               $carr_nr = $node->find('Lic')->string_value;
                       
                       #push @
                       #push @
            
                       # Start
                   my $node_overlay = $row->find('AbCarr/Warr');
        
                   foreach my $node ( $node_overlay->get_nodelist ) {
        
                      $overlay = $node->find('@VD')->string_value;    
                      $billa = $node->find('Billa1')->string_value;
                      $billb = $node->find('Billb2')->string_value;   
+          
                                   
                                  #push @
                                  #push @
                                  #push @
                                  
                       } # End
                       
                       
               # Start
               my $node_percent = $row->find('AbCarr/ADcar');

               foreach my $node ( $node_percent->get_nodelist ) {

                  $percent = $node->find('@REFI')->string_value;    
                  $rref = $node->find('Carr')->string_value;
                  
                  #push @
                  #push @
               } #End
            
            



            } #End AbCarr
            
            
push @replication, $replication;
} #End replication



#Get all results from the parsing and input all of it into a DB.

my $table = 'test';
my @fields = qw( replication part_no add_sample_ref fname carr carr_nr
+ overlay billa billb percent rref );

my $fields = join(', ', @fields);

my $values = join(', ', map { $dbh->quote($_) } @all_my_data_here{@fie
+lds});

$sql = "INSERT into $table ($fields) values ($values)";

$sth = $dbh->prepare($sql);
$sth->execute();
$sth->finish();

[download]

In reply to Inserting Multiple Records in DB. by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.