http://qs1969.pair.com?node_id=71192

UPDATE - DBIx::XML_RDB has been replaced by the new and improved XML::Generator::DBI. Please refer to XML::Generator::DBI Tutorial instead.

DBIx_XML_RDB is a module created by Matt Sergeant for creating XML documents from database queries. For example, if these rows:

+---------+--------------------+-------------+ | artist | title | album | +---------+--------------------+-------------+ | Genesis | Firth of Fifth | Seconds Out | | Genesis | I Know What I Like | Seconds Out | +---------+--------------------+-------------+
were returned from a query like so:
SELECT * FROM SONGS WHERE ARTIST='Genesis'
DBIx::XML_RDB would produce this XML text:
<?xml version="1.0"?> <DBI driver="database:host"> <RESULTSET statement="SELECT * FROM SONGS WHERE ARTIST='Genesis'"> <ROW> <artist>Genesis</artist> <title>Firth of Fifth</title> <album>Seconds Out</album> </ROW> <ROW> <artist>Genesis</artist> <title>I Know What I Like</title> <album>Seconds Out</album> </ROW> </RESULTSET> </DBI>
And here is the code - note however that the arguments to connect to a data source that I use are not the same as the ones in the documentation. I tested this with MySQL, and the example in the documentation did not work for me. After searching through some documention and using a little trial and error I found a combination of arguments that worked.
use strict; use DBIx::XML_RDB; my $xmlout = new DBIx::XML_RDB(qw(database:host driver user passwd)) || die "Failed to make new xmlout"; $xmlout->DoSql("SELECT * FROM SONGS WHERE ARTIST='Genesis'"); print $xmlout->GetData();
Very simple and easy. Even encodes binary data with UTF-8. Instead of printing, you can pass the goods to an XML parser:
# XML::Simple my $config = XMLin($xmlout->GetData); # XML::Parser my $p1 = new XML::Parser; $p1->parse($xmlout->GetData); # XML::Twig my $t = new XML::Twig(); $t->parse($xmlout->GetData);
Two scripts are provided with the module: sql2xml.pl and xml2sql.pl, for extracting data from one database (say Oracle) and inserting it into another (such as Sybase). Both work as promised, although the arguments for xml2sql.pl would have you believe you are trying to invoke xls2sql.pl. Be sure and study the source code for xml2sql.pl - lot's of good insight into XML parsing.

Now for a minor nitpick that is probably unjustified - there is no way to insert a style sheet link via the available API. This module is a great candidate for stylesheets, the author even mentions using it to allow the client's browser to render the data. I had to resort to this little hack to 'attach' a style sheet to my output:

my $out = $xmlout->GetData; my $css = qq|<?xml-stylesheet type="text/css" href="songs.css"?>|; $out =~ s/(<\?.*\?>)/$1\n$css/; # now the first three lines of $out are: <?xml version="1.0"?> <?xml-stylesheet type="text/css" href="songs.css"?> <DBI driver="database:host">
To give the author credit - there is probably a better way to achieve the same effect, but it would be trivial to add a couple of methods to specify a CSS Stylesheet or better yet, an XSL Stylesheet, which has table support.

I really like this module, I see a lot of potential for it.


As a final example, here is a CGI script that prints out an XML file, ready for parsing by an XML compliant browser. The XSL Stylesheet is included as well, just substitute your favorite database and column names. I recommend saving the script with a .xml extension and configuring your web server to treat .xml extensions as executable CGI scripts. If you use Apache, something like the following will work for the Internet Explorer browser:

Alias /xml/ "/usr/local/apache/xml/" <Directory "/usr/local/apache/xml"> AllowOverride None Options ExecCGI AddHandler cgi-script .xml DirectoryIndex index.xml Order allow,deny Allow from all </Directory>
Now you can save the file as index.xml and access it from your browser with http://localhost/xml/
# index.xml (a CGI script in disguise) use strict; use CGI qw(:standard); use DBIx::XML_RDB; my $xmlout = new DBIx::XML_RDB(qw(database:host driver user passwd)) || die "Failed to make new xmlout"; $xmlout->DoSql(" select artist, title, album, year from mp3.songs order by artist,year "); my $out = $xmlout->GetData; my $xsl = qq|<?xml-stylesheet type="text/xsl" href="songs.xsl"?>|; $out =~ s/^(<\?.*\?>)/$1\n$xsl/; print header(-type=>'text/xml'); print $out; ########################################################### # songs.xsl <?xml version='1.0'?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"> <xsl:template match="/"> <html> <body> <table border="2" bgcolor="#C0C0C0"> <tr> <th>Artist</th> <th>Title</th> <th>Album</th> <th>Year</th> </tr> <xsl:for-each select="DBI/RESULTSET/ROW"> <tr> <td><xsl:value-of select="artist"/></td> <td><xsl:value-of select="title"/></td> <td><xsl:value-of select="album"/></td> <td><xsl:value-of select="year"/></td> </tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet>