by Marta Lwin

This is an example of scraping any xml data, and inserting it into a database, as well as writing it to a file. Once you have any data comming in, serial, or xml, you can use this code to do the same. You'll need to change the database name, and all that specific type information, to match your database and file.

#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Net::HTTP;

#########################################################
# Connect to the xml feed- in this case weather data from www.weather.gov 		
#########################################################

use Net::HTTP;
my $s = Net::HTTP->new(Host => "www.weather.gov") || die $@;
$s->write_request(GET => "/data/current_obs/KNYC.xml", 'User-Agent' => "Mozilla/5.0");
my($code, $mess, %h) = $s->read_response_headers;
my $a;
while (1) {
my $buf;
my $n = $s->read_entity_body($buf, 1024);
$all .= $buf;
die "read failed: $!" unless defined $n;
last unless $n;
#print $buf;
}


#########################################################
# Define our DB info
#########################################################

if    ($0=~m#^(.*)\\#){ $execDir = "$1"; }  # Win/DOS
elsif ($0=~m#^(.*)/# ){ $execDir = "$1"; }  # Unix
else  {`pwd` =~ /(.*)/; $execDir = "$1"; }  # Unix

# Get Login Info
require "$execDir/info.pl";

$database = $mySqlUsername;
$data_source = "dbi:mysql:$database";
$username = $mySqlUsername;
$password = $mySqlPassword;

#########################################################
# Connect to the database.
#########################################################

my $dbh = DBI->connect($data_source, $username, $password,
{'RaiseError' => 1, 'PrintError' => 1});


#########################################################
# user regular expressions to grab xml
######################################################### 
$direction = "wind_dir";
$mph = "wind_mph";
$_ = $all; #"<wind_dir>West</wind_dir>";

	if ( ~m/<wind_dir>([^<]+)</i ) { $direction = $1;}
	if ( ~m/<wind_mph>([^<]+)</i ) { $mph = $1;}      
print "wind direction is $direction and speed is $mph\n";


#########################################################
# insert into database
######################################################### 

$SqlStatement .= "INSERT INTO nycWind (wind_dir,wind_mph,time_change ) ";
$SqlStatement .= "VALUES ('$direction','$mph', NOW()) ";
$affected_rows = $dbh->do($SqlStatement);



#########################################################
#select from both databases 
#########################################################
$direction_id = 0;
$SqlStatement = "SELECT id FROM wind_dirID ";
$SqlStatement .= "WHERE direction = '$direction' ";


$sth = $dbh->prepare($SqlStatement);
$sth->execute();

while (@row_array = $sth->fetchrow_array())
{       $direction_id = $row_array[0];
}

print "wind_dir id is $direction_id and speed is $mph\n";
print "$direction_id,$mph\n";

$sth->finish();

#########################################################
#write to file
#########################################################

$myDir = "/home/yourfiledirectory/windsketch/";
$myFilename = "weatherdata.txt";
$fullFilename = "$myDir/$myFilename";


open OUTPTR, ">$fullFilename" ;

print OUTPTR "$direction_id,$mph\n";

close OUTPTR;


#########################################################
# Disconnect from the database.
#########################################################   
$dbh->disconnect ();
exit(0);