macjo14

Dynamically sorting a JDBC Query using XSLT

Discussion created by macjo14 Employee on Aug 20, 2015

The JDBC Query assertion does not allow for dynamically defining the Order By value in a JDBC query. You can get around this by generating XML result, casting it to text/xml message type variable and using an XSLT stylesheet - ugly but functional. Pass the Order By values as parameters and set them as context variables before calling the Apply XSL Transformation assertion:

 

<xsl:stylesheet version="1.0" xmlns:L7j="http://ns.l7tech.com/2012/08/jdbc-query-result" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

 

<xsl:param name="parameter.sortOrder" select="ascending"/>

<xsl:param name="parameter.sortBy" select="id"/>

 

<xsl:template match="/L7j:jdbcQueryResult">

    <xsl:copy>

        <xsl:choose>

          <xsl:when test="$parameter.sortOrder = 'descending'">

            <xsl:for-each select="L7j:row">

              <xsl:sort order="descending" select="L7j:col[@name=$parameter.sortBy]"/>

              <xsl:copy-of select="."/>

            </xsl:for-each>

          </xsl:when>

          <xsl:otherwise>

            <xsl:for-each select="L7j:row">

               <xsl:sort order="ascending" select="L7j:col[@name=$parameter.sortBy]"/>

               <xsl:copy-of select="."/>

            </xsl:for-each>

          </xsl:otherwise>

        </xsl:choose>

    </xsl:copy>

</xsl:template>

 

</xsl:stylesheet>

Outcomes