Transforming mysql results to JSON using XSLT

The option '-X' of mysql produces a XML output:

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A \
-e 'select * from knownGene where chrom="chr1" limit 2' -X -D hg19

<?xml version="1.0"?>
<resultset statement="select * from knownGene where chrom=&quot;chr1&quot; limit 2
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="name">uc001aaa.3</field>
<field name="chrom">chr1</field>
<field name="strand">+</field>
<field name="txStart">11873</field>
<field name="txEnd">14409</field>
<field name="cdsStart">11873</field>
<field name="cdsEnd">11873</field>
<field name="exonCount">3</field>
<field name="exonStarts">11873,12612,13220,</field>
<field name="exonEnds">12227,12721,14409,</field>
<field name="proteinID"></field>
<field name="alignID">uc001aaa.3</field>
</row>

<row>
<field name="name">uc010nxq.1</field>
<field name="chrom">chr1</field>
<field name="strand">+</field>
<field name="txStart">11873</field>
<field name="txEnd">14409</field>
<field name="cdsStart">12189</field>
<field name="cdsEnd">13639</field>
<field name="exonCount">3</field>
<field name="exonStarts">11873,12594,13402,</field>
<field name="exonEnds">12227,12721,14409,</field>
<field name="proteinID">B7ZGX9</field>
<field name="alignID">uc010nxq.1</field>
</row>
</resultset>

I wrote a simple xslt stylesheet transforming this XML to JSON so I can easily use those sql results in a dynamic HTML page. The stylesheet is available at:

http://code.google.com/p/lindenb/source/browse/trunk/src/xsl/sql2json.xsl

Example

:

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A \
-e 'select * from knownGene where chrom="chr1" limit 2' > query.xml
xsltproc sql2json.xsl query.xml
[
{
"name":"uc001aaa.3",
"chrom":"chr1",
"strand":"+",
"txStart":11873,
"txEnd":14409,
"cdsStart":11873,
"cdsEnd":11873,
"exonCount":3,
"exonStarts":"11873,12612,13220,",
"exonEnds":"12227,12721,14409,",
"proteinID":"",
"alignID":"uc001aaa.3"
},
{
"name":"uc010nxq.1",
"chrom":"chr1",
"strand":"+",
"txStart":11873,
"txEnd":14409,
"cdsStart":12189,
"cdsEnd":13639,
"exonCount":3,
"exonStarts":"11873,12594,13402,",
"exonEnds":"12227,12721,14409,",
"proteinID":"B7ZGX9",
"alignID":"uc010nxq.1"
}]

The stylesheet uses two optional parameters: var=name defines a javascript variable named 'var' and ucsc=true interprets some fields from the UCSC database: for example 'exonStarts' will be treated as an array of integers.

xsltproc --stringparam var genes --stringparam ucsc true sql2json.xsl query.xml
var genes=[
{
"name":"uc001aaa.3",
"chrom":"chr1",
"strand":"+",
"txStart":11873,
"txEnd":14409,
"cdsStart":11873,
"cdsEnd":11873,
"exonCount":3,
"exonStarts":[11873,12612,13220],
"exonEnds":[12227,12721,14409],
"proteinID":"",
"alignID":"uc001aaa.3"
},
{
"name":"uc010nxq.1",
"chrom":"chr1",
"strand":"+",
"txStart":11873,
"txEnd":14409,
"cdsStart":12189,
"cdsEnd":13639,
"exonCount":3,
"exonStarts":[11873,12594,13402],
"exonEnds":[12227,12721,14409],
"proteinID":"B7ZGX9",
"alignID":"uc010nxq.1"
}
];

That's it,
Pierre