convert database data to xml file

itortu
itortu used Ask the Experts™
on
how can a XML file be written using database data using Perl?

the attached script (solution provided by expert on this forum) retrieves the database data and dumps it on a text file.

I would like to do the same, but instead that manually creating the XML tags, can a module be use?
testscript.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use XML::DOM Perl module to dynamically build an XML tree from the database query results.

Author

Commented:
how much would this change the current scrip that you helped me to convert to perl on the other question?
If you XML::DOM installed, this should do it:
#!/usr/local/bin/perl
use warnings;
use strict;

use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

use XML::Generator::DBI;
use XML::Handler::YAWriter;
use XML::DOM;

$doc = XML::DOM::Document->new();
my $xml_pi = $doc->createXMLDecl ('1.0');
my $root = $doc->createElement('DATA');


my $objConn = Win32::OLE->new("ADODB.Connection");
my $objCommand = Win32::OLE->new("ADODB.Command");
my $objRecordSet = Win32::OLE->new("ADODB.Recordset");
$objConn->Open("Driver={Webtrends ODBC Driver};Server=msnwinwebtrend1;Port=7099;Database=flrdacbkoji.wct;Uid=******;Pwd=*******;AccountId=1;Language=english;ProfileGuid=KlDwrNbKgm6;SSL=0;");
$objCommand->{"ActiveConnection"} = $objConn;
$objCommand->{"CommandText"} = "SELECT * FROM Pages WHERE Titles <> '' AND URLs IS NOT NULL AND Titles NOT LIKE 'PowerHouse TV : 404 Error Page' AND Titles NOT LIKE '302 Found' AND URLs <> 'http://powerhousetv.com/' AND URLs <> 'http://powerhousetv.com/index.htm' AND URLs <> 'http://powerhousetv.com/wcm/idcplg/' ORDER BY Visits DESC";
$objRecordSet->{"CursorLocation"} = 3; #adUseClient
$objRecordSet->{"CursorType"} = 0;     #adOpenForwardOnly
$objRecordSet->{"LockType"} = 1;       #adLockReadOnly;

$objRecordSet = $objCommand->Execute();

open OUT, ">output.txt";

my $rec = $doc->createElement('RECORD');
$root->appendChild($rec);
while (!$objRecordSet->EOF)
{
	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "Titles");
	  $rec->appendChild($col);
	  $val = $col->createTextNode($objRecordSet->Fields("Titles")->value);
	  $col->appendChild($val);

	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "URLs");
	  $rec->appendChild($col);
	  $val = $col->createTextNode($objRecordSet->Fields("URLs")->value);
	  $col->appendChild($val);

	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "Visits");
	  $rec->appendChild($col);
	  $val = $col->createTextNode($objRecordSet->Fields("Visits")->value);
	  $col->appendChild($val);

	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "Views");
	  $rec->appendChild($col);
	  $val = $col->createTextNode($objRecordSet->Fields("Views")->value);
	  $col->appendChild($val);

      print OUT $objRecordSet->Fields("Titles")->value, ", ";
      print OUT $objRecordSet->Fields("URLs")->value, ", ";
      print OUT $objRecordSet->Fields("Visits")->value, ", ";
      print OUT $objRecordSet->Fields("Views")->value, "\n";
      $objRecordSet->MoveNext();
}

print $xml_pi->toString;
print $root->toString;

$root->printToFile ("data.xml");

Open in new window

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Author

Commented:
is this script executed from the command line, or can it be just double clicked?

Author

Commented:
the script is not creating any file, not output.txt or data.xml

how can I find out what the error might be?

if the sql results are going to be saved as xml, does the script still needs this part:

print OUT $objRecordSet->Fields("Titles")->value, ", ";
      print OUT $objRecordSet->Fields("URLs")->value, ", ";
      print OUT $objRecordSet->Fields("Visits")->value, ", ";
      print OUT $objRecordSet->Fields("Views")->value, "\n";

thank you very much.
You can remove those print OUT statements. I removed those and added a specific directory for xml file. Change it to what you want. Try the code below:


#!/usr/local/bin/perl
use warnings;
use strict;

use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

use XML::Generator::DBI;
use XML::Handler::YAWriter;
use XML::DOM;

my $doc = XML::DOM::Document->new();
my $xml_pi = $doc->createXMLDecl ('1.0');
my $root = $doc->createElement('DATA');


my $objConn = Win32::OLE->new("ADODB.Connection");
my $objCommand = Win32::OLE->new("ADODB.Command");
my $objRecordSet = Win32::OLE->new("ADODB.Recordset");
$objConn->Open("Driver={Webtrends ODBC Driver};Server=msnwinwebtrend1;Port=7099;Database=flrdacbkoji.wct;Uid=******;Pwd=*******;AccountId=1;Language=english;ProfileGuid=KlDwrNbKgm6;SSL=0;");
$objCommand->{"ActiveConnection"} = $objConn;
$objCommand->{"CommandText"} = "SELECT * FROM Pages WHERE Titles <> '' AND URLs IS NOT NULL AND Titles NOT LIKE 'PowerHouse TV : 404 Error Page' AND Titles NOT LIKE '302 Found' AND URLs <> 'http://powerhousetv.com/' AND URLs <> 'http://powerhousetv.com/index.htm' AND URLs <> 'http://powerhousetv.com/wcm/idcplg/' ORDER BY Visits DESC";
$objRecordSet->{"CursorLocation"} = 3; #adUseClient
$objRecordSet->{"CursorType"} = 0;     #adOpenForwardOnly
$objRecordSet->{"LockType"} = 1;       #adLockReadOnly;

$objRecordSet = $objCommand->Execute();

my $rec = $doc->createElement('RECORD');
$root->appendChild($rec);
my ($col, $val);

while (!$objRecordSet->EOF)
{
	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "Titles");
	  $rec->appendChild($col);
	  $val = $doc->createTextNode($objRecordSet->Fields("Titles")->value);
	  $col->appendChild($val);

	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "URLs");
	  $rec->appendChild($col);
	  $val = $doc->createTextNode($objRecordSet->Fields("URLs")->value);
	  $col->appendChild($val);

	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "Visits");
	  $rec->appendChild($col);
	  $val = $doc->createTextNode($objRecordSet->Fields("Visits")->value);
	  $col->appendChild($val);

	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "Views");
	  $rec->appendChild($col);
	  $val = $doc->createTextNode($objRecordSet->Fields("Views")->value);
	  $col->appendChild($val);

      $objRecordSet->MoveNext();
}

print $xml_pi->toString;
print $root->toString;

$root->printToFile ("C:\\Temp\\data.xml");

Open in new window

Author

Commented:
the script works nice with the only problem that there is no closing tag after each record is written.

example;

there are four fields being pull from database

titles, urls, visits, views.

when the xml is written (thank you!) it writes a big record containing all the separate records

the file contents are included on the data.txt file attached



data.txt
My bad. The $rec should be created inside the loop for each record:

#!/usr/local/bin/perl
use warnings;
use strict;

use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

use XML::Generator::DBI;
use XML::Handler::YAWriter;
use XML::DOM;

my $doc = XML::DOM::Document->new();
my $xml_pi = $doc->createXMLDecl ('1.0');
my $root = $doc->createElement('DATA');


my $objConn = Win32::OLE->new("ADODB.Connection");
my $objCommand = Win32::OLE->new("ADODB.Command");
my $objRecordSet = Win32::OLE->new("ADODB.Recordset");
$objConn->Open("Driver={Webtrends ODBC Driver};Server=msnwinwebtrend1;Port=7099;Database=flrdacbkoji.wct;Uid=******;Pwd=*******;AccountId=1;Language=english;ProfileGuid=KlDwrNbKgm6;SSL=0;");
$objCommand->{"ActiveConnection"} = $objConn;
$objCommand->{"CommandText"} = "SELECT * FROM Pages WHERE Titles <> '' AND URLs IS NOT NULL AND Titles NOT LIKE 'PowerHouse TV : 404 Error Page' AND Titles NOT LIKE '302 Found' AND URLs <> 'http://powerhousetv.com/' AND URLs <> 'http://powerhousetv.com/index.htm' AND URLs <> 'http://powerhousetv.com/wcm/idcplg/' ORDER BY Visits DESC";
$objRecordSet->{"CursorLocation"} = 3; #adUseClient
$objRecordSet->{"CursorType"} = 0;     #adOpenForwardOnly
$objRecordSet->{"LockType"} = 1;       #adLockReadOnly;

$objRecordSet = $objCommand->Execute();

my ($rec, $col, $val);

while (!$objRecordSet->EOF)
{
           $rec = $doc->createElement('RECORD');
           $root->appendChild($rec);
	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "Titles");
	  $rec->appendChild($col);
	  $val = $doc->createTextNode($objRecordSet->Fields("Titles")->value);
	  $col->appendChild($val);

	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "URLs");
	  $rec->appendChild($col);
	  $val = $doc->createTextNode($objRecordSet->Fields("URLs")->value);
	  $col->appendChild($val);

	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "Visits");
	  $rec->appendChild($col);
	  $val = $doc->createTextNode($objRecordSet->Fields("Visits")->value);
	  $col->appendChild($val);

	  $col = $doc->createElement('FIELD');
	  $col->setAttribute ("Name", "Views");
	  $rec->appendChild($col);
	  $val = $doc->createTextNode($objRecordSet->Fields("Views")->value);
	  $col->appendChild($val);

      $objRecordSet->MoveNext();
}

print $xml_pi->toString;
print $root->toString;

$root->printToFile ("C:\\Temp\\data.xml");

Open in new window

Author

Commented:
wow thank you so much, it works wonderfully!

please tell me, will this script also work on a Linux server, obviously making sure that the required modules are installed? or will further modifications be necessary to make it work?

once again, thank you so much, you rock!

Author

Commented:
Hi jmatix,

Just following up. Do you think I should open another question to find out if the script will work on a Linux server?

Thanks much
Win32::OLE functionality will not work on Linux server.

Author

Commented:
what would be your recommendation?

Author

Commented:
jmatix, thank you so much, you have been so helpful, and your solution rocks!

Author

Commented:
 one question you may know the answer.

I can run the script on a  windows server, but the XML file needs to be saved on a Linux server. Is  there a way using Perl, that the file can access the secure server and  write the file?
                           

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial