Friday, July 9, 2010

Getting MySQL data into XML easily

I have been experimenting with XML lately and have found a great article for using XSLT to make an html table using an sql query from a database.
http://blog.mclaughlinsoftware.com/2008/08/29/querying-oracle-directly-from-xml/
It will use some of the instructions from this blog post:
download Xalan and unzip it to C:/xalan-j_2_7_1
http://xml.apache.org/xalan-j/downloads.html
Then download the mysql J connector:
Go into the zip and take it out and put it in the xalan folder
Mine was put here:
C:/xalan-j_2_7_1/mysql-connector-java-5.1.13-bin.jar
Next make an xsl file and name it mysql.xsl and put this in it:

<?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>

Next run these commands from command prompt:
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\xalan.jar;.
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\serializer.jar
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\xercesImpl.jar
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\xml-apis.jar
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\xsltc.jar
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\mysql-connector-java-5.1.13-bin.jar
java org.apache.xalan.xslt.Process -XSL mysql.xsl > mysql.html
Now you should have an html document called mysql.html all ready to look at in a browser with the database tables showing in html format.
I will show an example:

item_titleitem_asinitem_release_date
"Good Eats with Alton Brown, Vol. 6 (3 - Pack): Tossed Around, Veggie Eats, Breakfast Eats 2"143499
"Good Eats with Alton Brown Vol. 8, Delicious Dishes (3 - Pack): Condiment Nation, Ocean Edibles 2, Veggie Eats 2"143510
Sports Medicine Imaging DVD: Single User477044
Musculoskeletal MRI DVD: Single User477087
Posted by at No comments:

Wednesday, July 7, 2010

Converting html codes to html entities

I found this helpful site to convert the html entities like < into &lt;
http://centricle.com/tools/html-entities/

And then url encode / decoding is good too:
http://meyerweb.com/eric/tools/dencoder/
Posted by at No comments:

XSLT and iTunes parsing into HTML table

I will now show some code that I have been tweaking from a site:

It wasn't working so I had to play with it some until I understood how it works. XSLT is powerful when it comes to converting xml to any format you want.
I will show the example xslt style document I changed from the site above to make really cool looking iTunes tables showing all your music sorted by genre and the times and sizes of each.

First download the Xalan

Find your itunes xml document in My Music folder in My Documents for your current user then open the iTunes folder and copy the iTunes Music Library.xml to your xalan folder and rename it to itunes.xml

Here is where mines was found:
C:\Documents and Settings\mike\My Documents\My Music\iTunes

Now make an itunes.xsl file and put this in it:


<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:key name="songsByAlbum" match="dict"
use="string[preceding-sibling::key[1]='Album']"/>

<xsl:key name="songsByGenre" match="dict"
use="string[preceding-sibling::key[1]='Genre']"/>


<xsl:template match="/plist/dict/dict">
<html>
<style>
table,th,td{border:1px solid black;padding:3px;}
th{text-align:left;background-color:#3333CC;}
.indent{border:0px;width:10px;}
.genre{background-color:#7094FF;}
.album{background-color:#3366FF;color: #222}
</style>
<body>
<table>
<tr><th colspan="2">Genre / Album</th><th>Artist</th><th>Time</th><th>Size</th></tr>
<xsl:for-each select="dict[generate-id(.)=
generate-id(key('songsByGenre',string)[1])]">
<xsl:sort select="string[preceding-sibling::key[1]='Genre']"/>
<xsl:for-each select="key('songsByGenre',string)[1]">

<xsl:call-template name="albumsInGenre">
<xsl:with-param name="genre"
select="string[preceding-sibling::key[1]='Genre']"/>
</xsl:call-template>



</xsl:for-each>
</xsl:for-each>

</table>
</body>
</html>
</xsl:template>


<xsl:template name="albumsInGenre">
<xsl:param name="genre"/>

<tr><td class="genre" colspan='5'><b><xsl:value-of select="$genre"/></b></td></tr>

<xsl:variable name="song" select="/plist/dict/dict/dict"/>
<xsl:for-each select="$song[generate-id(.)=
generate-id(key('songsByAlbum',string[preceding-sibling::key[1]='Album'])[1])]">
<xsl:sort select="string[preceding-sibling::key[1]='Album']"/>
<xsl:for-each select="key('songsByAlbum',string[preceding-sibling::key[1]='Album'])
[string[preceding-sibling::key[1]='Genre']=$genre][1]">
<tr>
<td class="indent"> </td>
<td class="album" align='left'><xsl:call-template name="albumName"/></td>
<td class="album" align='left'><xsl:call-template name="artistName"/></td>
<td class="album" align='right'><xsl:call-template name="iTunesTimeAlbum"/></td>
<td class="album" align='right'><xsl:call-template name="iTunesSizeTotal"/></td>
</tr>
</xsl:for-each>
</xsl:for-each>
</xsl:template>

<xsl:template name="albumName">
<xsl:value-of select="string[preceding-sibling::key[1]='Album']"/>
</xsl:template>

<xsl:template name="artistName">
<xsl:choose>
<xsl:when test="true[preceding-sibling::key[1]='Compilation']">
<i>Compilation</i>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="string[preceding-sibling::key[1]='Artist']"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

<xsl:template name="iTunesTimeAlbum">
<xsl:variable name="tracksInAlbum"
select="key('songsByAlbum',string[preceding-sibling::key[1]='Album'])"/>
<xsl:variable name="t"
select="sum($tracksInAlbum/integer[preceding-sibling::key[1]='Total Time'])"/>
<xsl:call-template name="formatTime">
<xsl:with-param name="t" select="$t"/>
</xsl:call-template>
</xsl:template>

<xsl:template name="formatTime">
<xsl:param name="t"/>
<xsl:if test="$t != 0">
<xsl:variable name="h" select="floor(($t div (1000*60*60)))"/>
<xsl:variable name="m" select="floor(($t div (1000*60)) mod 60)"/>
<xsl:variable name="s" select="floor(($t div 1000) mod 60)"/>
<xsl:if test="$h != 0"><xsl:value-of select="$h"/>:</xsl:if>
<xsl:value-of select="format-number($m,'00')"/>:<xsl:value-of select="format-number($s,'00')"/>
</xsl:if>
</xsl:template>

<xsl:template name="iTunesSizeTotal">
<xsl:variable name="tracksInAlbum"
select="key('songsByAlbum',string[preceding-sibling::key[1]='Album'])"/>
<xsl:variable name="s"
select="sum($tracksInAlbum/integer[preceding-sibling::key[1]='Size'])"/>
<xsl:value-of select="format-number($s div (1000*1000),'00.00')"/>MB
</xsl:template>

<xsl:template name="formatSize">
<xsl:param name="s"/>
<xsl:if test="$s != 0">
<xsl:value-of select="floor($s)"/>GB
</xsl:if>
</xsl:template>

<xsl:template name="iTunesSizeTotalorig">
<xsl:variable name="s" select="sum(dict/integer[preceding-sibling::key[1]='Size'])"/>
<xsl:value-of select="floor($s div (1000*1000))"/>MB
</xsl:template>

</xsl:stylesheet>



Now you just open command prompt and paste this into it:
set CLASSPATH=C:\JavaDev\Java5\ojdbc5.jar;.
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\xalan.jar;.
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\serializer.jar
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\xercesImpl.jar
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\xml-apis.jar
set CLASSPATH=%CLASSPATH%;C:\xalan-j_2_7_1\xsltc.jar

Next paste this line:
java org.apache.xalan.xslt.Process -IN itunes.xml -XSL itunes.xsl -HTML > itunes.html

Now after pressing enter you should have an html file in the xalan folder and it will be formated like this:

Genre / AlbumArtistTimeSize
General Classical
Bibbidi Bobbidi BachScott Tennant07:3107.22MB
Soundtrack
Les Miserables 10th Anniversary Concert Disc 11995 Royal Albert Hall Concert Cast03:1503.12MB
Les Miserables 10th Anniversary Concert Disc 21995 Royal Albert Hall Concert Cast03:0903.02MB

Isn't that cool? You could also use the path to the itunes xml file in the xalan if you like what you see and then it will just convert to html what ever you have now.
This works for me too if you are in the xalan folder or where ever you have your itunes.xsl file and where you want your itenues.html to be written or you may change those as well:
java org.apache.xalan.xslt.Process -IN "C:\Documents and Settings\mike\My Documents\My Music\iTunes\iTunes Music Library.xml" -XSL itunes.xsl -HTML > itunes.html
Posted by at 6 comments:

learning XSLT and xml to csv to Oracle or MySQL

I have been learning some XSLT
Here is a good place to start learning how to use it to convert xml to html.
This is the page on W3 schools teaching about the for-each loop that lets you loop through the xml code then you can select the xml elements you want to grab.

You can also convert xml to csv and then to a database if you want. Here is a good tutorial on how to do this:

Also if you want to take a csv file and put it into a MySQL database this is what you do:
DROP TABLE IF EXISTS movie;
CREATE TABLE movie ( role VARCHAR(30) , actor VARCHAR(30) , movie VARCHAR(60)) engine=memory;

TRUNCATE movie;
LOAD DATA LOCAL INFILE 'path_to_csv/movie.csv'
INTO TABLE movie
FIELDS TERMINATED BY '\t'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

The csv is taken into the table with the load data local infile command.
Drop table will drop the table so you can create it again next time you use this command so it is a re-runnable script.
Truncate just removes all data in the table. The Oracle script on blog.mclaughlinsoftware.com uses an external table which makes it so when you read from the table movie you are really reading from the csv file so when ever you change the csv the data in the database is automatically changed when you read from it next. Like:
SELECT * FROM movie;





Posted by at No comments:
Subscribe to: Posts (Atom)