saving sql data in xml formatted file

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I have a perl script that retrieves data from a database and saves the results in a text file. I need to change this so the database results are saved in an XML file. I would like to format/ generate the XML tags automatically, but I do not know how to do this, what changes will be necessary on the script. Any help on how to do this in the code, would be really nice. This is my current script:

#!/usr/local/bin/perl
use warnings;
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

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=sdfhadgdjlah;Por
+t=7099;Database=flrdacbkoji;Uid=adkljd;Pwd=fadadk;lmam;AccountId=1;La
+nguage=english;ProfileGuid=KlDwrNbKgm6;SSL=0;");
$objCommand->{"ActiveConnection"} = $objConn;
$objCommand->{"CommandText"} = "SELECT * FROM Pages WHERE Titles <> ''
+ AND URLs IS NOT NULL AND Titles NOT LIKE '%404 Error Page%' AND Titl
+es NOT LIKE '%302 Found' AND URLs <> 'http://powerhousetv.com/' AND U
+RLs <> 'http://domain.com/index.htm' AND URLs <> 'http://domain.com/w
+cm/idcplg/' ORDER BY Visits DESC";
$objRecordSet->{"CursorLocation"} = 3; #adUseClient
$objRecordSet->{"CursorType"} = 0;     #adOpenForwardOnly
$objRecordSet->{"LockType"} = 1;       #adLockReadOnly;

$objRecordSet = $objCommand->Execute();

open OUT, ">output.txt";

while (!$objRecordSet->EOF)
{
      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();
}
[download]

Comment on saving sql data in xml formatted file Download Code

Replies are listed 'Best First'.
Re: saving sql data in xml formatted file by samtregar (Abbot) on Oct 19, 2010 at 01:11 UTC
I like to use XML::Writer for this kind of thing. I can handle any quoting you need and it will make sure your tags are balanced. -sam	[reply]
Re: saving sql data in xml formatted file by aquarium (Curate) on Oct 18, 2010 at 23:14 UTC
Have you looked at or attempted to use any of the XML modules? I think a good starting point would be there. Also, for the XML output have you decided what the output is going to look like (i.e. XML schema/dtd)?..as XML is not freeform like text. Lastly, the SQL query construct is absolutely horrible, and you should seriously consider refactoring how that's done. Which will greatly aid in making the code less likely to fail in trivial variations in the data, and more flexible and maintainable. Also looks like the input data is of questionable quality. For example a server http response code would be much better to use instead of the human readable response, which differs between web server setups. Anyway, hope you don't take this personally, as my opinion is just based on the code presented. Which could do with improvement to make it reliable. the hardest line to type correctly is: stty erase ^H	[reply]
Re: saving sql data in xml formatted file by kcott (Archbishop) on Oct 18, 2010 at 23:20 UTC
The answer rather depends on what XML application you're intending to use. The process is not particularly dissimilar to what you already have. You may need something like: `print OUT '<page>'; print OUT '<title>', $objRecordSet->Fields("Titles")->value, '</title> +'; ... print OUT '<view>', $objRecordSet->Fields("Views")->value, '</view>'; print OUT '</page>';` [download] or perhaps: `print OUT '<page '; print OUT 'title="', $objRecordSet->Fields("Titles")->value, '" '; ... print OUT 'view="', $objRecordSet->Fields("Views")->value, '" '; print OUT '/>';` [download] All of that will probably need to be wrapped in a top-level element. Some header information may be required. -- Ken	[reply] [d/l] [select]


Your skill will accomplish what the force of many cannot
	PerlMonks