Home > Coding > Paging XML data from SQL

Paging XML data from SQL

2009/03/28

We all know the story – we’ve got a web page that’s pulling a lot of data from the database. In ASP.NET, the Gridview object will handle the pagination for you, but you still have to pull all of the data from the server. Using the ROW_NUMBER() option, we can return just the records we need to display for the page, defined by the starting row number and the number of rows to return.

This is the SQL snippet generally presented as the solution:

SELECT TOP (@rows) * FROM
(
    SELECT fips, county, state, pop2000, area
    ROW_NUMBER() OVER (ORDER BY fips) AS num
    FROM dbo.Counties
) AS a
WHERE num > @startrow

which gives us @rows worth of counties from the @startrow position after the results are sorted.

But what if you want the results returned as XML data? My solution is to use a common table element to determine the result set first and then format the XML from the CTE.

WITH CTE AS
(
   SELECT TOP (@rows) * FROM
   (
       SELECT fips, county, state, pop2000, area
       ROW_NUMBER() OVER (ORDER BY fips) AS num
       FROM dbo.Counties
   ) AS a
   WHERE num > @startrow
) 
SELECT fips AS '@FIPS', county, state, pop2000, area
FROM CTE
FOR XML PATH('County'), ROOT('Counties') 

You get an XML stream like this:

<Counties>
        <County FIPS=’01001′>
                  <county>Autauga County</county>
                  <state>Alabama</state>
                  <pop2000>500</pop2000>
                  <area>20</area>
        </County>
        <County FIPS=’01003′> 
        …..
</Counties> 

(These are not the real statistics by the way.) And usually I also return the row number as an element/attribute.

From here, we can use an XSL transform to parse the data into our final format. Now, I haven’t done any query analysis on this or compared it to a temp table situation, but it is quick to implement.

Advertisements
Categories: Coding Tags: , , , , ,
%d bloggers like this: