Layer7 API Management

 View Only

Dynamically sorting a JDBC Query using XSLT

  • 1.  Dynamically sorting a JDBC Query using XSLT

    Broadcom Employee
    Posted Aug 19, 2015 09:24 PM

    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>