Friday, October 14, 2011

PERL scripting: Export data from MySQL database in XML format

I have a MySQL database containing all the data but the application I am trying to use only supports XML. How can I export data from MySQL database in XML format?

Here is your answer. You need to select a programming language that support MySQL connection and does pretty good string manipulation. I found PERL programming very easy and powerful doing both MySQL connection and String Manipulation.

Assumptions: I will be running my PERL script on the same server with MySQL database. MySQL Username and Password are 'username' and 'password' respectively. Database name is 'phonesDB' and table name is 'phones'.

MySQL

Database: phonesDB
Table: phones



full_name phone_number department
Peter Griffin 25962 Technology
Stewie Griffin 25963 Finance
Sam Burns 25234 HR

You should have some basic concept of XML, MySQL and PERL programming knowledge.

[Note: Make sure that you have perl-DBD-mysql, perl-DBI and perl installed. If those package are missing, you can use YaST, YUM, ZYPPER, APT-GET to install those missing packages based on your distro.]

Let's write the PERL script that will access our MySQL database and generate XML file for us.

#vi myPerlscript.pl
#!/usr/bin/perl
use strict;
use DBI;
use DBD::mysql;

#connect to the database phonesDB using login credentials
my $dbh = DBI->connect("DBI:mysql:phonesDB","username","password",{RaiseError => 1, PrintError => 0});

#run the SELECT query
my $sth = $dbh->prepare("SELECT full_name,phone_number,department FROM phones");
$sth->execute();

print "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";
print "<directory>\n";
print " <local>true</local>\n";
print " <sorted>true</sorted>\n";
print " <name>Company_Name</name>\n";
while (my ($full_name,$phone_number,$department) = $sth->fetchrow_array ())
{

print " <user>\n";

#Using string manipulation techniques available in PERL scripting
#Extract first and last name from full name
#index() function returns the occurrence of $myspace in $full_name
#substr() function allows to copy part of a string from another by specifying beginning position and the length of string
my $myspace =" ";
my $myindex =index($full_name,$myspace);
my $last_name=substr($full_name,$myindex+1);
my $first_name=substr($full_name,0,$myindex);

print " <name>$last_name, $first_name</name>\n";
print " <company>$site_name</company>\n";
print " <contact>\n";
print " <type>phone</type>\n";
print " <uri>sip:$phone_number</uri>\n";
print " </contact>\n";
print " </user>\n";
}

#Disconnect the database connection
$dbh->disconnect ();
print "</directory>\n";



Let's make the script executable
#chmod u+w myPerlscript.pl

Now run the script, you will get output in XML format
#./myPerlscript.pl

If you want to save the output of the script to the file,
#./myPerlscript.pl > myXMLfile.xml


Output:

2 comments:

Sansaro said...

Very useful blog. My only question is where have to i place the myPerlscript.pl file?

Devendra said...

Thank you Sansaro. You can place the script where ever you want to. It really doesn't matter. I keep it in a folder where I want to generate XML file. I hope this helps.