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 11, 2011 @ 00:41
    Luke Johnson
    0

    SQL select top 10 from db_table, in XSLT

    How do I go about selecting a number of records from an SQL database by using XSLT?

    One option I have been using works according to this set up:

      <xsl:variable name = "newsItem" select="SQL:GetDataSet('DBname', 'select id, date, title, description, thumbnail, articletype from webcontent_news WHERE articletype = 2', 'newsItem')"/>
            <!-- PAGING SETTINGS -->
            <xsl:variable name="recordCount" select="'6'"/>
            <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="/">
                <span id="article-wrapper" 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">
                            <article class = "newsstory">
                                <a>
                                  <xsl:attribute name = "href">/bcast/news/article.aspx?id=<xsl:value-of select = "id" />
                                    </xsl:attribute>
                                    <xsl:attribute name = "title">
                                        <xsl:value-of select = "title" />
                                    </xsl:attribute>
                                    <span class = "title">
                                       <xsl:value-of select = "title" />
                                    </span>
                                    <span class = "publish_info">By [insert author] | <xsl:value-of select = "date" /></span>
                                </a>
                            </article>
                            
                        </xsl:if>
                    </xsl:for-each>
                </span><!-- article-wrapper -->
        </xsl:template>

    This works for some senarios, but it requires an HTML tag to wrap the XSLT file so that start="$startRecord+$recordCount" can reside in it. However, this doesn't work when I want to call the url of a file into an audio player's src. 

    Is there a way to access a SQL database with XSLT using "SELECT TOP 10 from DB_Table" ?

    Thank-you,

    Luke

  • Nigel Wilson 915 posts 2002 karma points
    Aug 11, 2011 @ 02:30
    Nigel Wilson
    0

    Hi

    Could you simply add "TOP 10" to your sql query as follows:

    Also I would assume you might want to have an "order by" so have also added that:

    select top 10 id, date, title, description, thumbnail, articletype from webcontent_news WHERE articletype = 2 order by date desc

    Is this what you were meaning ?

    Cheers

    Nigel

  • praveity 100 posts 125 karma points
    Aug 11, 2011 @ 12:38
    praveity
    0

    Hi Luke,

    You can try one of the Umbraco Package called jesper at url

    http://our.umbraco.org/projects/developer-tools/sql-for-xslt-(jespercom)

Please Sign in or register to post replies

Write your reply to:

Draft