
Jezza
New User
May 4, 2009, 3:50 PM
Post #1 of 2
(5394 views)
|
XML -> DB using Xpath and DBI
|
Can't Post
|
|
Hello, 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 (?,?,?,?,?)", undef, $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 (?,?,?,?)", undef, $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; $sth3->execute($name,$id_object); } $dbh->disconnect (); And my xml file is in attachment. XML file has this scheme:
<oval_definitions> <definitions> <definition> </definition> <definition> </definition> ... </definitions> <tests> </tests> <objects> </objects> <states> </states> </oval_definitions> 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?
|