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



Jezza
New User

May 4, 2009, 3:50 PM


Views: 5855
XML -> DB using Xpath and DBI

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

Code
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:

Code
<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?



1arryb
User

May 6, 2009, 12:13 PM


Views: 5819
Re: [Jezza] XML -> DB using Xpath and DBI

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.

Cheers,

Larry