|Syntactic Confectionery Delight|
DBIx::XML_RDB Tutorialby jeffa (Bishop)
|on Apr 10, 2001 at 02:28 UTC||Need Help??|
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:
were returned from a query like so:
DBIx::XML_RDB would produce this XML text:
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.
Very simple and easy. Even encodes binary data with UTF-8. Instead of printing, you can pass the goods to an XML parser:
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:
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:
Now you can save the file as index.xml and access it from your browser with http://localhost/xml/