MySQL, XSLT & Xalan Queries
I posted how to connect to an Oracle database from an XSLT library file back in August 2008. It’s an event driven XML approach that can support web page development. One of my students wanted to do the same thing against MySQL. He quickly saw that it was simply a matter of the switching the JDBC library. He’s got the whole idea bundled on his blog here.
XSLT Library File to Query MySQL Database ↓
Unfold this if you’d like to see the XLST code he’s posted on his blog for MySQL. You’ll find that only line #10 (below) required a change. If you’re new to XSLT, you may find Doug Tidwell’s XSLT, 2nd Edition book very helpful.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | <?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sql="org.apache.xalan.lib.sql.XConnection" extension-element-prefixes="sql"> <xsl:output method="html" /> <xsl:template match="/"> <xsl:variable name="movies" select="sql:new('com.mysql.jdbc.Driver','jdbc:mysql:///storedb','student','student')" /> <xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" /> <xsl:variable name="queryResults" select="sql:query($movies,'SELECT i.item_title, i.item_asin, i.item_release_date FROM storedb.item i')" /> <html> <head><title>MySQL Result Set</title></head> <body style="font-family: sans-serif;"> <table border="1" cellpadding="5"> <tr> <xsl:for-each select="$queryResults/sql/metadata/column-header"> <th><xsl:value-of select="@column-label" /></th> </xsl:for-each> </tr> <xsl:apply-templates select="$queryResults/sql/row-set/row" /> </table> </body> </html> <xsl:value-of select="sql:close($movies)" /> </xsl:template> <xsl:template match="row"> <tr><xsl:apply-templates select="col" /></tr> </xsl:template> <xsl:template match="col"> <td><xsl:value-of select="text()" /></td> </xsl:template> </xsl:stylesheet> |
Hope this helps those looking for a solution.