limit sql records with xslt

Luke Johnson 61 posts 80 karma points

Aug 05, 2011 @ 23:29

XSLT

My website makes heavy use of external SQL databases, so I need a way to limit the records returned from the various tables.

Is there a simple way to limit SQL records with XSLT? I have successfully connected to the databases, but when I access the information, I get everything in the specified columns. I am trying to limit the records to 16.

I have accomplished this in classic ASP when originally constructing the website, but I need to access it with XSLT instead for this Umbraco project. The classic ASP example is at http://briercrest.ca/redesign2/college/ (the news slider below the banner images).

I was attempting to limit the results with <xsl:if test="position() <= 16" />, but I don't think I'm on the right track.

Thanks,

Luke

Copy Link

Luke Johnson 61 posts 80 karma points

Aug 06, 2011 @ 00:01

I found the solution! For anyone interested, here is the entire XSLT file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xsl:stylesheet [ <!ENTITY nbsp "&#x00A0;"> ]>
<xsl:stylesheet 
  version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:msxml="urn:schemas-microsoft-com:xslt"
  xmlns:umbraco.library="urn:umbraco.library" xmlns:Exslt.ExsltCommon="urn:Exslt.ExsltCommon" xmlns:Exslt.ExsltDatesAndTimes="urn:Exslt.ExsltDatesAndTimes" xmlns:Exslt.ExsltMath="urn:Exslt.ExsltMath" xmlns:Exslt.ExsltRegularExpressions="urn:Exslt.ExsltRegularExpressions" xmlns:Exslt.ExsltStrings="urn:Exslt.ExsltStrings" xmlns:Exslt.ExsltSets="urn:Exslt.ExsltSets" xmlns:SQL="urn:SQL" xmlns:NarniaExt="urn:NarniaExt" xmlns:tagsLib="urn:tagsLib" xmlns:BlogLibrary="urn:BlogLibrary" 
  exclude-result-prefixes="msxml umbraco.library Exslt.ExsltCommon Exslt.ExsltDatesAndTimes Exslt.ExsltMath Exslt.ExsltRegularExpressions Exslt.ExsltStrings Exslt.ExsltSets SQL NarniaExt tagsLib BlogLibrary ">


<xsl:output method="xml" omit-xml-declaration="yes"/>

<xsl:param name="currentPage"/>

<!-- SQL Here -->
  <xsl:variable name = "newsItem" select="SQL:GetDataSet('DatabaseName', 'select id, date, title, description, thumbnail, articletype from table_name', 'newsItem')"/>
        <!-- PAGING SETTINGS -->
        <xsl:variable name="recordCount" select="'16'"/>
        <xsl:variable name="startRecord">
            <xsl:choose>
                <xsl:when test="umbraco.library:Request('newsItemStartRecord') != ''">
                    <xsl:value-of select="umbraco.library:Request('newsItemStartRecord')"/>
                </xsl:when>
                <xsl:otherwise>0</xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
        <xsl:variable name="endRecord" select="$startRecord+$recordCount"/>

        <xsl:template match="/">

            <div id="slides" start="{$startRecord+1}">
                <xsl:for-each select="$newsItem//newsItem">
                    <xsl:sort select="date" order="descending" />
                    <xsl:if test="position() &gt;= $startRecord and position() &lt;= $endRecord">

                        <xsl:if test="position() mod 4 = 1">
                            <xsl:text disable-output-escaping="yes"><![CDATA[<div class = "slide">]]></xsl:text>
                        </xsl:if>
                        <article class = "collegenews">
                            <a>
                                <xsl:attribute name = "href">
                                    http://www.briercrest.ca/alumni/news-article.asp?id=<xsl:value-of select = "id" />
                                </xsl:attribute>
                                <xsl:attribute name = "title">
                                    <xsl:value-of select = "title" />
                                </xsl:attribute>
                                <img>
                                    <xsl:attribute name="src">
                                        /webdocs/bcast/news/images/<xsl:value-of select="thumbnail"/>
                                    </xsl:attribute>
                                    <xsl:attribute name = "alt">
                                        <xsl:value-of select = "title" />
                                    </xsl:attribute>
                                </img>
                                <span class = "collegenews_title">
                                    <xsl:value-of select = "articletypedescription" />
                                    <span class = "collegenews_subtitle">
                                        <xsl:value-of select = "title" />
                                    </span>
                                </span>
                            </a>
                        </article>
                        <xsl:if test="position() mod 4 = 0 or position() = last()">
                            <xsl:text disable-output-escaping="yes"><![CDATA[</div>]]></xsl:text>
                        </xsl:if>
                        
                    </xsl:if>
                </xsl:for-each>
            </div><!-- slides -->
    </xsl:template>

</xsl:stylesheet>

Copy Link

Chriztian Steinmeier 2798 posts 8788 karma points MVP 7x admin c-trib

Aug 06, 2011 @ 00:03

Hi Luke,

That's actually the way you'd do it - if your data is sorted the way it should be, you can ask for just the first 16 by adding a predicate to the selection:

<!-- Grab the movies -->
<xsl:variable name="data" select="document('http://mydata.com/movies/')" />

<!-- Process the first 16 -->
<xsl:apply-templates select="$data/movies/movie[position() &lt;= 16]" />

<!-- Template for a single movie -->
<xsl:template match="movie">
    <p>
        <xsl:value-of select="title" />
    </p>
</xsl:template>

But if you need to sort them first, you'll need to grab everything and use a for-each with the nested sort(s) and then check the position() value:

<!-- Grab the movies -->
<xsl:variable name="data" select="document('http://mydata.com/movies/')" />

<!-- Sort by releaseDate DESC and take the top 16 -->
<xsl:for-each select="$data/movies/movie">
    <xsl:sort select="releaseDate" data-type="text" order="descending" />

    <xsl:if test="position() &lt;= 16">
        <xsl:apply-templates select="." />
    </xsl:if>
</xsl:for-each>

<!-- Template for a single movie -->
<xsl:template match="movie">
    <p>
        <xsl:value-of select="title" />
<xsl:apply-templates select="releaseDate" mode="date" />
    </p>
</xsl:template>

/Chriztian

Copy Link

Chriztian Steinmeier 2798 posts 8788 karma points MVP 7x admin c-trib

Aug 06, 2011 @ 00:06

OK, if you're actually querying a SQL database, I'd say it makes much more sense to just do the SELECT TOP 16 ... ORDER BY stuff in there...

/Chriztian

Copy Link

Luke Johnson 61 posts 80 karma points

Aug 08, 2011 @ 15:34

Where would I make a regular SQL query (SELECT TOP 16 ... ORDER BY)? In the XSLT file? I didn't think the two could mix (but I'm no expert...)

Copy Link

Chriztian Steinmeier 2798 posts 8788 karma points MVP 7x admin c-trib