Paging XML Data to a Web Client

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 25Else if paging forward     Add 25 to the start record number and the last record numberElse if paging back     Subtract 25 from the start record number and the last record numberEnd ifIf 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 XMLEnd ifIf displaying all records then     Use the entire XML data island fileElse     Use an XSLT file to select the records between start record number and last record number from the XML data island fileEnd ifSend 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:

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

                                   RE-SORT.XSL