CURRENT PROJECTS
loading
CATEGORIES AND POSTS
loading
overset
DEVELOPMENT LOG FOR JIM PALMER
Posted 02/21/2008 in ajax


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">'</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>

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''"' city='mammoth lakes''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>

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.
comments
loading
new comment
NAME
EMAIL ME ON UPDATES
EMAIL (hidden)
URL
MESSAGE TAGS ALLOWED: <code> <a> <pre class="code [tab4|tabX|inline|bash]"> <br>
PREVIEW COMMENT
TURING TEST
gravatar