Re: SQL to XML

Let’s see if the simplest possible strategy would be a start... You execute the query, retrieve each row with fetchrow_hashref(), and use any one of several XML packages that know how to “dump a hashref out as an XML structure.” If you have complete discretion as to what the XML format could be at this point, then this would be a quick-n-dirty solution and sometimes Q&D is the cat’s meow.

On the other hand, this is a pivotal decision-point that could have very long term implications for your project, and you don’t want both your name and your lineage to be profaned by generations of programmers who continued to work at the company after your asterisk was fired. ;-) ;-) (It is even worse when you find yourself profaning your own name, when you realize that you made a hasty but numb-skull decision that by now you can’t reverse.) So, think it over, how you really want that file to look.

“Each query” would be wrapped in some kind of container structure that identifies which query it is. While building the XML file you may find that you need to write the outputs to several different intermediate files, then bring them all together into one ... which can be done through a little bit of file-copying. And if the data is or could be large, you would want to do that, since “holding it all in-memory” would send you straight to Virtual Memory Thrashing Hell, and ... “abandon all hope, ye who enter there.”

Replies are listed 'Best First'.
Re^2: SQL to XML by packetstormer (Monk) on Sep 02, 2011 at 15:05 UTC
Is the following considered bad practice? Could I just keep adding queries as $recs3, $recs4 etc? Obviously I would amend the XMLout to print to the one output file? `my $dbh = DBI->connect('DBI:mysql:MYDATABASE','user','password') or die DBI->errstr; my $recs = $dbh->selectall_arrayref('SELECT * FROM contents',{ Columns + => {} }); my $resc2 = $dbh->selectall_arrayref('SELECT * FROM contacts',{ Column +s => {} }); # Convert to XML where each hash element becomes an XML element my $xml = XMLout( {contents => $recs contacts => $recs2}, NoAttr => 1 ); print $xml; $dbh->disconnect;` [download]	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: SQL to XML
by packetstormer (Monk) on Sep 02, 2011 at 15:05 UTC

my $dbh = DBI->connect('DBI:mysql:MYDATABASE','user','password')
  or die DBI->errstr;

my $recs = $dbh->selectall_arrayref('SELECT * FROM contents',{ Columns
+ => {} });
my $resc2 = $dbh->selectall_arrayref('SELECT * FROM contacts',{ Column
+s => {} });

# Convert to XML where each hash element becomes an XML element
my $xml = XMLout( {contents => $recs
           contacts => $recs2},
           NoAttr => 1 );

print $xml;

$dbh->disconnect;
[download]

[reply]
[d/l]