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:

References:
MySQL connection using PERL
String Manipulation using PERL