Re^4: Building SQL Query on the fly

I edited my posted you commented on using SQL::Abstract, but I like your way better with the sorting. It looks like it will work well, but since other parameters are being passed besides just column names (like order by, where clauses) I can't really push it all in which is why I was doing it one by one, but then again, that's the only way I thought of doing it. So in my edited posted, I mentioned doing

 while (my $row = $sth->fetchrow_hashref ){
        <<<<SOME KIND OF XML::SIMPLE PUSH HERE???>>>>>
 }
[download]

I am confused on how to make the column names the element tags, and the returned values in the hash ($row), into XML tags. I will continue to read about the XML::Simple, but all pointers are extremely appreciated.

Comment on Re^4: Building SQL Query on the fly Download Code

Replies are listed 'Best First'.
Re^5: Building SQL Query on the fly by graff (Chancellor) on Oct 08, 2007 at 05:35 UTC
If you provide a snippet of real data from the table, and a snippet of what you want the XML to look like, I expect others will have no trouble pointing you in the right direction. But you might very well figure it out on your own first. In the meantime, I'll just say that your loop over the fetched database rows will be building (array elements within) a hash structure, and the structure will be passed to the XMLout method of your XML::Simple object. I'm not sure, but your structure loading loop might be something like this: `my %xml_hash = ( rootnode => [] ); # top XML node has an empty array_r +ef as value while ( my $row = $sth->fetchrow_hashref ) { push @{$xml_hash{rootnode}}, $row; } print $xmlobj->XMLout( \%xml_hash );` [download] I'm probably missing some important details there, and I'm sorry that the XML::Simple docs are so huge. Good luck, and good night.	[reply] [d/l]
Re^6: Building SQL Query on the fly by hallikpapa (Scribe) on Oct 08, 2007 at 06:28 UTC
Thanks! The following seems to work. I appreciate all the help! `$sth->execute(@bind); my %xml_hash = ( dataset => [ ] ); while (my $row = $sth->fetchrow_hashref ){ push @{$xml_hash{dataset}}, $row; } my $cgi = CGI->new; print $cgi->header('text/xml'); my $testxml = XMLout( \%xml_hash, NoAttr => 1, RootName => data +set, ); print $testxml; $sth->finish();` [download]	[reply] [d/l]