CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
Search Posts SEARCH
Who's Online WHO'S
Log in LOG

Home: Perl Programming Help: DBI:
XML -> DB using Xpath and DBI


New User

May 4, 2009, 3:50 PM

Post #1 of 2 (6056 views)
XML -> DB using Xpath and DBI Can't Post

I have a problem with my project, which should populate MySQL DB with data from xml file. There is my script:

use strict; 
use DBI;
use XML::XPath;
use XML::XPath::XMLParser;

my $dbh = DBI->connect ("DBI:mysql:test", "root", "*****", { RaiseError => 1, PrintError => 0});
my $xp = XML::XPath->new (filename => "com.redhat.rhsa-2008.xml");
my $nodelist = $xp->find ("/oval_definitions");
foreach my $row ($nodelist->get_nodelist ())

# populating of definition
$dbh->do (
"INSERT INTO definition (ID_definition,title,severity,description,date) VALUES (?,?,?,?,?)",
$row->find ('definitions/definition/@id')->string_value (),
$row->find ('definitions/definition/metadata/title')->string_value (),
$row->find ('definitions/definition/metadata/advisory/severity')->string_value (),
$row->find ('definitions/definition/metadata/description')->string_value (),
$row->find ('definitions/definition/metadata/advisory/issued/@date')->string_value ()
# populating of criterion
foreach my $row2 ($nodelist->get_nodelist ())
$dbh->do (
"INSERT INTO criterion (ID_criterion,ID_criterion_object,ID_criterion_state,comment) VALUES (?,?,?,?)",
$row2->find ('tests/rpminfo_test/@id')->string_value (),
$row2->find ('tests/rpminfo_test/object/@object_ref')->string_value (),
$row2->find ('tests/rpminfo_test/state/@state_ref')->string_value (),
$row2->find ('tests/rpminfo_test/@comment')->string_value ()
foreach my $row3 ($nodelist->get_nodelist ())
my $sth3 = $dbh->prepare ("UPDATE criterion SET name=? WHERE ID_criterion_object=?");
my $id_object = $row3->find ('objects/rpminfo_object/@id')->string_value;
my $name = $row3->find ('objects/rpminfo_object/name')->string_value;
$dbh->disconnect ();

And my xml file is in attachment. XML file has this scheme:


But it doesn't work as it should. It works just for one occurance of element <definition> and its sub-elements. Same problem is with elements criterion...
Do you know why? Do you have any tips how to solve this problem?


May 6, 2009, 12:13 PM

Post #2 of 2 (6020 views)
Re: [Jezza] XML -> DB using Xpath and DBI [In reply to] Can't Post

Hi Jezza,

I don't use XPath much, so my debug advice is generic: Some XML parsers are stateful, so if you are expecting $nodelist->get_nodelist() to start at the top of the file at "foreach my $row2", you may be disappointed. If this is the case with XPath, you will either have to refactor your script to put processing for definition, criterion, etc. into the same foreach loop OR read the XPath perldoc and figure out how to reset $nodelist between loops.




Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives