SQL Server Magazine

Consider the following scenario. Your customer has a Web-based reporting system that uses client-side Internet Explorer (IE) data islands. The current version pulls all the records to the client at once and uses Extensible Style Language Transformations (XSLT) on the client side to transform the data to a columnar format. This version also has client-side data sorting (by clicking on the field column). Your customer wants to change this system to one that pages the data to the client, 25 records at a time, to speed up the report's initial presentation—downloading and performing transformations on a few thousand records is too slow. But the customer, of course, still wants to sort the data by the field columns and to be able to view all the records.

When the report returns the records from the query, you could simply display the first 25 records. Then if a user pages ahead, you could display the next 25 records. But running the query every time seems like a waste of resources. You could use ADO to page the records down, but you need a stateless design (this Web site is, after all, WELL designed). You could use some fancy, elaborate, "smart" stored procedure that creates a temporary table for every user who runs the report and then perform selects out of the temporary table for the paged records. But that's a lot of hits to the database and a lot of maintenance to get rid of the huge number of temporary tables. I'm going to share with you an XML solution that I used to tackle this very scenario—using Windows 2000, SQL Server 2000, IE 5.0+, and Microsoft XML Parser (MSXML) 3.0.

The Solution
My solution uses a data island—not a client-side, IE data island, but a server-side file data island. Below is the pseudocode for the solution:

If this is the first run of the query then
     Run the query
	 Save the resulting XML to a file on the server
	 Set the start record number to 0 and the last record number to 25
Else if paging forward
     Add 25 to the start record number and the last record number
Else if paging back
     Subtract 25 from the start record number and the last record number
End if

If resorting then
     Transform the XML data island file with an XSLT file that resorts the records
	 Overwrite the XML file on the server with the resulting XML
End if

If displaying all records then
     Use the entire XML data island file
Else
     Use an XSLT file to select the records between start record number and last record
	 number from the XML data island file
End if
Send the XML to the client

Voila! Trust me, this approach is much simpler than it sounds. You simply need five hidden input tags on the report page that you post over:

• a hidden input that holds the current first-record number on the page
• a hidden input that holds the desired action (e.g., show first page, page up, page down)
• a hidden input that holds the field to sort by
• a hidden input that holds the sort direction
• a hidden input that holds the sort field's date type

You need to either pass parameters to your Extensible Style Language (XSL) files or use the Document Object Model (DOM) to change the paging range of the pagination XSL file and the sorting properties of the re-sorting XSL file.

The pagination and the re-sorting code appear below.

PAGINATION.XSL

<?xml version="1.0"?> 
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html"/>
<xsl:param name="thestart"/>

<xsl:template match="*">
     <!-- only get some of the records from the master XML file -->
	 <!--  the 0 and 2 below can be parameters passed to this XSL file -->
	   <xsl:for-each select="row[position() &gt; 0 and position() &lt; 2]" >
	   
	   <!-- copy the records to a new xml output -->
	      <xsl:copy>
		       <xsl:copy-of select="@*"/>
			   <xsl:apply-templates/>
			   </xsl:copy>
		</xsl:for-each>
		<!-- calculate the grandtotal for all records-->
	<grandtotal><xsl:value-of select="sum(//@Amount_Paid)"/></grandtotal>
</xsl:template>

</xsl:stylesheet>

RE-SORT.XSL

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html"/>
<xsl:template match="/">
     <xsl:for-each select="ROOT/row">
	 <!--  the 3 attributes below can be parameters passed to this file -->
	 <xsl:sort select="@ListPrice" order="ascending" data-type="number"/>
	 <xsl:copy>
	      <xsl:copy-of select="@*"/>
		  <xsl:apply-templates/>
	 </xsl:copy>
     </xsl:for-each>
</xsl:template>
</xsl:stylesheet>

End of Article