How to Get Paged XML Data From SQL 

By Albert Hovingh On December 07, 2016

How to Get Paged XML Data From SQL 
Originally when I started a project to export my SQL data over to a web service with XML, I imagined it would be a pretty standard thing, right? Quite a few web services rely on XML, and any large amount of data would have to be paged if you are sending it across different servers. Unable to find a tutorial on this topic I decided to fill in the gap and write one myself.

There are multiple ways to do this. The simplest is just by using our handy dandy OFFSET and just getting the rows we want. To get XML from this, just add a FOR XML Path on the end, and you’re done.
DECLARE @CurrentPosition INT
DECLARE @CurrentPage INT

@PageSize = 10
@CurrentPage = 4 --zero based, so your first page would be zero, this is actually the 5th page
@CurrentPosition = @PageSize * (@CurrentPage -1)

SELECT [rows]
FROM [Table]
ORDER BY [id_of_table] DESC --you need this so that we know where to page from

That works great unless you’re using an earlier version of SQL than 2012 (which I happened to be using). Also, this may not be the most efficient method to use if you need to cache some data, and page it that way (see the third method for more info on that solution).

In case you find yourself stuck in legacy land, you can go ahead and try out a CTE. If you’re in the stone age with less than SQL 2000 the last method should still work for you. Be aware that I haven’t tested it, so use it at your own peril.

DECLARE @pQuoteNo varchar(30)
    ,@Page int
    ,@RecordsPerPage int

SET @Page = 2
SET @RecordsPerPage = 1000

DECLARE @QuoteLinesXML XML, @ParamDefinition nvarchar(500)

;WITH PageNumbers AS(
        ,SUM([Quantity]) AS Quantity
        ,DueDate AS DeliveryDate
        ,ROW_NUMBER() OVER(ORDER BY PartNumber) ID
    FROM [Test].[dbo].[QuotePartDelivery]
    GROUP BY DueDate, PartNumber
SELECT @QuoteLinesXML = (
FROM    PageNumbers
WHERE   ID  BETWEEN ((@Page - 1) * @RecordsPerPage + 1)
        AND (@Page * @RecordsPerPage)

Notice in the above solution I selected the data into a varchar variable. That way I can access it when I output it for a sub-select in a different query. The only thing you have to be wary about is that your data must be able to fit in the varchar that you select. In my case I have a bunch of orders that need order lines: this gets me paged order lines that I can pair with an order.

I was sending out a bunch of orders, each one had 10 different order lines. I would wrap it with the XML that the order had. 
Since the data is in a variable already, I can just do this to return my data in a sub-select:

  FOR XML PATH('Order'), ROOT('Orders'), ELEMENTS XSINIL) AS Orders

And voila, you have nested XML data, and now it’s ready to use.

This works great, unless your data is constantly changing and you don't want any duplicate information getting into your XML document. It's also not very good if you have a lot of data and a lot of pages that you need to page through, or if you need to find a varchar to get your unique data for paging.

You could insert them all into a temp table, and then perform the operation on that temp table to save some time. However, a problem presents itself when you want to run this multiple times -- and perhaps at the same time.

This is where you can use global temp tables and add a GUID to the end of the name. It's also useful if you have multiple people requesting a certain page of data. If you have a lot of data, this may take awhile to get the first page, but after that it loads up the temp table with everything you need. 
ALTER PROC [dbo].[spuForecastToPlex]

@pQuoteNo varchar(30)
,@Page int
,@RecordsPerPage int = 10
,@GUID uniqueidentifier = null OUTPUT 
-- will output the extension of the table.
-- This parameter should be sent by the application:
-- First time it should be NULL and after, it should be
-- populated by the value that was sent back from the stored procedure.

SELECT @pQuoteNo AS QuoteNo
    ,SUM([Quantity]) AS Quantity
    ,'' AS [Description]
    ,DueDate AS DeliveryDate
    ,CAST(0 AS FLOAT) AS UnitPrice
    ,CAST(0 AS FLOAT) AS ExtendedPrice
INTO #ForecastQuoteLines
FROM [Test].[dbo].[QuotePartDelivery]
GROUP BY DueDate, PartNumber

DECLARE @FirstRec int, @LastRec int, @cmd varchar(4000), @sSQL nvarchar(500), @QuoteLinesXML XML, @ParamDefinition nvarchar(500)

--This is why the first run will take longer, we are putting all of our records in the temp table with a unique index before we start paging
--Our unique index is just row number now, not some ugly concatenation of three fields


    SET @cmd = 'SELECT RowNum=IDENTITY(INT,1,1)

        ,SUM([Quantity]) AS Quantity
        ,DueDate AS DeliveryDate

    INTO [##tmp_' + CONVERT(VARCHAR(40),@GUID) + ']
    FROM [Test].[dbo].[QuotePartDelivery]
    GROUP BY DueDate, PartNumber
    ON [##tmp_' + CONVERT(VARCHAR(40),@GUID) + '] (RowNum)'
    EXEC (@cmd)

SELECT @FirstRec = (@Page - 1) * @RecordsPerPage
SELECT @LastRec = (@Page * @RecordsPerPage + 1)

--Here we can dynamically select our XML data
SELECT @sSQL = N'SET @QuoteLinesOut = (
SELECT TOP (' + CONVERT(VARCHAR(20),@LastRec-1) + ') *
    FROM [##tmp_' + CONVERT(VARCHAR(40),@GUID) + ']
    WHERE RowNum > ' + CONVERT(VARCHAR(20),@FirstRec) +
    ' AND RowNum < ' + CONVERT(VARCHAR(20),@LastRec) +
    ' FOR XML PATH(''OrderLine''), ROOT(''OrderLines''), ELEMENTS XSINIL, TYPE’ + 

SET @ParamDefinition = N'@QuoteLinesOut XML OUTPUT';

--This allows us to get results from the dynamic SQL we created above
EXEC sp_executesql @sSQL, @ParamDefinition, @QuoteLinesOut=@QuoteLinesXML OUTPUT;


  FOR XML PATH('Order'), ROOT('Orders'), ELEMENTS XSINIL) AS Orders

With this example, we use sp_executesql to get output from a dynamic query so we can use GUIDs as a part of our table name. Now when we go back to fetch more pages, it's super fast. This will do the same thing as our CTE example, only with caching. Although I will admit if you are only looking to get a page or two, I wouldn't recommend this later method since it has to populate the temp table with all of the data.

Notice that I stored the lines in a separate variable when I called the orders. I found this to be especially helpful when I needed later on to make multiple levels on the XML document.

Hopefully using these methods, more people will be able to find out how to conveniently send a large amount of data through an XML web service. If any of you are curious how to do this same thing with JSON, leave us a comment, and I can write a SQL method that will do this same thing for JSON data for another blog post.
Click here to read more Advice posts
Start a Project with Us
Photo of the author, Albert Hovingh

About the author

A Grand Valley State University graduate, Albert is the guy who takes on new technology and learns it inside and out. At BizStream, he mostly does SharePoint and Kentico development, but you can put him on any project and it will be a success. Albert has four adorable kids, and is a shark when it comes to Foosball and anything XBox-related.

View other posts by Albert

Subscribe to Updates

Stay up to date on what BizStream is doing and keep in the loop on the latest with Kentico.