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

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

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

 



Jezza
New User

May 4, 2009, 3:50 PM

Post #1 of 2 (2875 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:

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

Post #2 of 2 (2839 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.

Cheers,

Larry

 
 


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

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