Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Luke Johnson 61 posts 80 karma points
    Aug 05, 2011 @ 23:29
    Luke Johnson
    0

    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() &lt;= 16" />, but I don't think I'm on the right track.

    Thanks,

    Luke

     

  • Luke Johnson 61 posts 80 karma points
    Aug 06, 2011 @ 00:01
    Luke Johnson
    0

    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>
  • Chriztian Steinmeier 2698 posts 8107 karma points MVP 3x admin c-trib
    Aug 06, 2011 @ 00:03
    Chriztian Steinmeier
    0

    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

  • Chriztian Steinmeier 2698 posts 8107 karma points MVP 3x admin c-trib
    Aug 06, 2011 @ 00:06
    Chriztian Steinmeier
    0

    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

  • Luke Johnson 61 posts 80 karma points
    Aug 08, 2011 @ 15:34
    Luke Johnson
    0

    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...)

  • Chriztian Steinmeier 2698 posts 8107 karma points MVP 3x admin c-trib
    Aug 08, 2011 @ 15:40
    Chriztian Steinmeier
    0

    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

     

Please Sign in or register to post replies

Write your reply to:

Draft