com.overset.dev» Blog Archive » Convert ADODB XML To JSON Through XSLT

2.21.08

Convert ADODB XML To JSON Through XSLT

Hot on the heels of my ADOtoWDDX XSLT1.0 script, here’s a way to take the ADODB XML recordSet and parse the data into a JSON array of objects (again, this will work with XSLT 1.0):

Here’s the XSLT:

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

    <xsl:output method="text"/> 

    <xsl:template match="/">

        <xsl:for-each select="*/*">

            <xsl:if test="contains(name(), 'data')">

[<xsl:for-each select="*">

    {<xsl:if test="contains(name(), 'row')">

                    <xsl:for-each select="@*">

        <xsl:value-of disable-output-escaping="yes" select="name()"/>:'<xsl:call-template name="escape-single-quote"><xsl:with-param name="text" select="."/></xsl:call-template>'<xsl:if test="not(position()=last())">,</xsl:if>

                    </xsl:for-each>

                </xsl:if>}<xsl:if test="not(position()=last())">,</xsl:if>

            </xsl:for-each>

]

            </xsl:if>

        </xsl:for-each>

    </xsl:template>

    <xsl:template name="escape-single-quote">

        <xsl:param name="text"/>

        <xsl:variable name="delimeter">&apos;</xsl:variable>

        <xsl:variable name="padded-delimeter" select="concat('\',$delimeter)"/>

        <xsl:choose>

            <!-- test if there are any delimeters in the string -->

            <xsl:when test="contains($text, $delimeter)">

                <!-- output the string before this delimeter -->

                <xsl:value-of disable-output-escaping="yes" select="substring-before($text, $delimeter)"/><xsl:value-of disable-output-escaping="yes" select="$padded-delimeter"/>

                <!-- recursive call to next block -->

                <xsl:call-template name="escape-single-quote"><xsl:with-param name="text" select="substring-after($text, $delimeter)"/></xsl:call-template>

            </xsl:when>

            <!-- otherwise end the recursion -->

            <xsl:otherwise>

                <xsl:value-of disable-output-escaping="yes" select="$text"/>

            </xsl:otherwise>

        </xsl:choose>

    </xsl:template>

</xsl:stylesheet>

Download this code: ADOtoJSON.xls

The example above contains a “escape-single-quote” function (aka template) to use to escape all single quotes which need to be escaped for the JSON document to be valid. This is based off several widely published concepts on using simple recursion similar to a C++ String Tokenizer concept. Of course, this is only for the 1999 XSL Transformations (XSLT) Version 1.0 specification - for maximum compatibility. XSLT2.0 has a nice replace() function which enables regular expressions, so nice.

Here’s an example ADODB XML string:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'

    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'

    xmlns:rs='urn:schemas-microsoft-com:rowset'

    xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'>
...

removed AttributeType information here

...
    </s:ElementType>

</s:Schema>
<rs:data>

    <z:row guest_no='1808354000000' first_name='JIMBOB' last_name='PALMERSKI'

         birth_date='1980-06-01T00:00:00' address_id='927000000000'

         area_code='(555)' phone='121-2121' area_cod2='     ' phone2='        '

         address='2443 unit 15&#x27;&#x27;&#x22;' city='mammoth lakes&#x27;&#x27;2'

         state='AK ' zip='12345'/>

    <z:row guest_no='1750384000000' first_name='WEBOPS' last_name='RULEZ;'

         birth_date='1981-01-01T00:00:00'/>

</rs:data>

</xml>

Download this code: ADO.xml

And here’s the XSLT actual transformation output:
[ { guest_no:'1808354000000', first_name:'JIMBOB', last_name:'PALMERSKI', birth_date:'1980-06-01T00:00:00', address_id:'927000000000', area_code:'(555)', phone:'121-2121', area_cod2:' ', phone2:' ', address:'2443 unit 15\'\'"', city:'mammoth lakes\'\'2', state:'AK ', zip:'12345' }, { guest_no:'1750384000000', first_name:'WEBOPS', last_name:'RULEZ;', birth_date:'1981-01-01T00:00:00' } ]

This is glorious for output conversion from the bloated ADODB default XML recordSet - especially when you’re not using the ADODB assemblies or are not in the windows environment.

1 Comment