I am trying to parse the following
XML into variables and then a MySQL
server. How do i deal with multiple rows? this is driving me crazy. TIA!
<event>
<event_datetimeGMT>2006-05-02 16:35</event_datetimeGMT>
<gamenumber>65661022</gamenumber>
<sporttype>Baseball</sporttype>
<league>Alt Runlines</league>
-
<participants>
-
<participant>
<participant_name>St Louis Cardinals</participant_name>
<contestantnum>901</contestantnum>
<rotnum>901</rotnum>
<visiting_home_draw>Visiting</visiting_home_draw>
<pitcher>S. Ponson</pitcher>
</participant>
-
<participant>
<participant_name>Cincinnati Reds</participant_name>
<contestantnum>902</contestantnum>
<rotnum>902</rotnum>
<visiting_home_draw>Home</visiting_home_draw>
<pitcher>D. Williams</pitcher>
</participant>
</participants>
-
<periods>
-
<period>
<periodnumber>0</periodnumber>
<period_description>Game</period_description>
<periodcutoff_datetimeGMT>2006-05-02 16:35</periodcutoff_datetimeGMT>
<period_status>O</period_status>
-
<moneyline>
<moneyline_visiting>null</moneyline_visiting>
<moneyline_home>null</moneyline_home>
</moneyline>
-
<spread>
<spread_visiting>1.5</spread_visiting>
<spread_adjust_visiting>-227</spread_adjust_visiting>
<spread_home>-1.5</spread_home>
<spread_adjust_home>207</spread_adjust_home>
</spread>
-
<total>
<total_points>null</total_points>
<over_adjust>null</over_adjust>
<under_adjust>null</under_adjust>
</total>
</period>
</periods>
</event>
<event>
<event_datetimeGMT>2006-05-02 19:35</event_datetimeGMT>
<gamenumber>65661024</gamenumber>
<sporttype>Baseball</sporttype>
<league>Alt Runlines</league>
-
<participants>
-
<participant>
<participant_name>
San Diego Padres</participant_name>
<contestantnum>905</contestantnum>
<rotnum>905</rotnum>
<visiting_home_draw>Visiting</visiting_home_draw>
<pitcher>W. Williams</pitcher>
</participant>
-
<participant>
<participant_name>San Francisco Giants</participant_name>
<contestantnum>906</contestantnum>
<rotnum>906</rotnum>
<visiting_home_draw>Home</visiting_home_draw>
<pitcher>M. Morris</pitcher>
</participant>
</participants>
-
<periods>
-
<period>
<periodnumber>0</periodnumber>
<period_description>Game</period_description>
<periodcutoff_datetimeGMT>2006-05-02 19:35</periodcutoff_datetimeGMT>
<period_status>O</period_status>
-
<moneyline>
<moneyline_visiting>null</moneyline_visiting>
<moneyline_home>null</moneyline_home>
</moneyline>
-
<spread>
<spread_visiting>-1.5</spread_visiting>
<spread_adjust_visiting>197</spread_adjust_visiting>
<spread_home>1.5</spread_home>
<spread_adjust_home>-217</spread_adjust_home>
</spread>
-
<total>
<total_points>null</total_points>
<over_adjust>null</over_adjust>
<under_adjust>null</under_adjust>
</total>
</period>
</periods>
</event>
-
<event>
<event_datetimeGMT>2006-05-02 23:05</event_datetimeGMT>
<gamenumber>65661025</gamenumber>
<sporttype>Baseball</sporttype>
<league>Alt Runlines</league>
-
<participants>
-
<participant>
<participant_name>Philadelphia Phillies</participant_name>
<contestantnum>907</contestantnum>
<rotnum>907</rotnum>
<visiting_home_draw>Visiting</visiting_home_draw>
<pitcher>J. Lieber</pitcher>
</participant>
-
<participant>
<participant_name>Florida Marlins</participant_name>
<contestantnum>908</contestantnum>
<rotnum>908</rotnum>
<visiting_home_draw>Home</visiting_home_draw>
<pitcher>S. Olsen</pitcher>
</participant>
</participants>
-
<periods>
-
<period>
<periodnumber>0</periodnumber>
<period_description>Game</period_description>
<periodcutoff_datetimeGMT>2006-05-02 23:05</periodcutoff_datetimeGMT>
<period_status>O</period_status>
-
<moneyline>
<moneyline_visiting>null</moneyline_visiting>
<moneyline_home>null</moneyline_home>
</moneyline>
-
<spread>
<spread_visiting>1.5</spread_visiting>
<spread_adjust_visiting>-275</spread_adjust_visiting>
<spread_home>-1.5</spread_home>
<spread_adjust_home>255</spread_adjust_home>
</spread>
-
<total>
<total_points>null</total_points>
<over_adjust>null</over_adjust>
<under_adjust>null</under_adjust>
</total>
</period>
</periods>
</event>
-
<event>
<event_datetimeGMT>2006-05-02 23:10</event_datetimeGMT>
<gamenumber>65661026</gamenumber>
<sporttype>Baseball</sporttype>
<league>Alt Runlines</league>
-
<participants>
-
<participant>
<participant_name>Washington Nationals</participant_name>
<contestantnum>909</contestantnum>
<rotnum>909</rotnum>
<visiting_home_draw>Visiting</visiting_home_draw>
<pitcher>M. O'Connor</pitcher>
</participant>
-
<participant>
<participant_name>New York Mets</participant_name>
<contestantnum>910</contestantnum>
<rotnum>910</rotnum>
<visiting_home_draw>Home</visiting_home_draw>
<pitcher>J. Maine</pitcher>
</participant>
</participants>
-
<periods>
-
<period>
<periodnumber>0</periodnumber>
<period_description>Game</period_description>
<periodcutoff_datetimeGMT>2006-05-02 23:10</periodcutoff_datetimeGMT>
<period_status>O</period_status>
-
<moneyline>
<moneyline_visiting>null</moneyline_visiting>
<moneyline_home>null</moneyline_home>
</moneyline>
-
<spread>
<spread_visiting>-1.5</spread_visiting>
<spread_adjust_visiting>204</spread_adjust_visiting>
<spread_home>1.5</spread_home>
<spread_adjust_home>-224</spread_adjust_home>
</spread>
-
<total>
<total_points>null</total_points>
<over_adjust>null</over_adjust>
<under_adjust>null</under_adjust>
</total>
</period>
</periods>
</event>
Here is my perl
#!/usr/bin/perl -wT
use strict;
use warnings;
use CGI;
use DBI;
use CGI::Carp qw(fatalsToBrowser);
use LWP::Simple;
use XML::Parser;
use XML::Simple;
use LWP::UserAgent;
use Data:
umper;
require "./top.pl";
require "./bot.pl";
print CGI::header();
# create hash to hold values for expected column names
my $str;
my $dbh = DBI->connect("dbi:mysql:sports_gamble:localhost:3306 ", "sports", "*****") or die "Can't open DB: $!";
my $url="******";
print $url;
my $source=get($url);
$_ = $source; #Perl VAR set to data from URL
my $xmlfile = $_;
my $parser = new XML::Parser(); $parser->setHandlers( Start => \&startElement, End => \&endElement, Char => \&characters);
$parser->parse($xmlfile);
#
#XML FILE HAS Two <participants> tags.
#
my %event;
my $event_datetimeGMT = lc($event{"event_datetimeGMT"});
my $gamenumber = lc($event{"gamenumber"});
my $league = lc($event{"league"});
my $participant_name = lc($event{"participant_name"});
print <<END_OF_HTML;
<br>Time:
$event_datetimeGMT <br>
GameNumber: $gamenumber<br>
<br>League: $league<br>
<br>part: $participant_name<br>
END_OF_HTML
my $type;
my @context;
sub startElement {
my ($p, $tag, %atts) = @_;
push @context, $tag;
if ($tag eq "gamenumber") {
$type = $atts{"type"};
}
}
sub characters {
my ($p, $text) = @_;
return unless $text =~ /\S/;
$text =~ s/^\s+//;
$text =~ s/\s+$//;
if ($context[-1] eq "event_datetimeGMT") {
$event{"event_datetimeGMT"} .= $text;
}
elsif ($context[-1] eq "gamenumber") {
$event{"gamenumber"} .= $text;
}
elsif ($context[-1] eq "league") {
$event{"league"} .= $text;
}
elsif ($context[-1] eq "participant_name") {
$event{"participant_name"} .= $text;
}
}
sub endElement {
my ($p, $tag) = @_;
pop @context;
}
