convert database data to xml file

You can use XML::DOM Perl module to dynamically build an XML tree from the database query results.

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");

Select allOpen in new window

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

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");

Select allOpen in new window

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

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!

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.

what would be your recommendation?

Try DBD::ODBC

http://search.cpan.org/~jurl/DBD-ODBC-1.13/ODBC.pm

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

 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?