limit sql records with xslt

Luke Johnson 61 posts 80 karma points

Aug 05, 2011 @ 23:29

Limit SQL records with 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 2800 posts 8791 karma points MVP 8x 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 2800 posts 8791 karma points MVP 8x 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 2800 posts 8791 karma points MVP 8x admin c-trib

Aug 08, 2011 @ 15:40

Hi Luke,

As far as I can see, you're using an extension function - SQL:GetDataSet() - which seems to take an SQL statement, right?

(The line that sets the $newsItem variable)

/Chriztian

Copy Link

is working on a reply...

This forum is in read-only mode while we transition to the new forum.

You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Flag this post as spam?

Limit SQL records with XSLT