By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

How to page SQL query result returned from FOR XML statement?

P: n/a
Gurus.
I do not know if it is possible, but here is what I want to do.
I want to allow user to page the SQL result, so he could decides to
return from row 10 to row 20, or row 100 to 200, without returns the
whole resultset. Every time he sends another request, I do not mind to
hit the database again, (I do not want to cache the result in the
middle tier server, scalability issue), and I know that I could achieve
this with CURSOR, but unfortunately the FOR XML is not allowed in a
CURSOR statement .
(I know that I could achieve what I want to do by writing custom code
in the middle tier, but I just want to see if there is a way to do this
on the database side.)

Any comments & suggestion is greatly appreciated.
Thanks in advance.

(I am using SQL2005)

John

Apr 25 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
John (jo*****@yahoo.com) writes:
I do not know if it is possible, but here is what I want to do.
I want to allow user to page the SQL result, so he could decides to
return from row 10 to row 20, or row 100 to 200, without returns the
whole resultset. Every time he sends another request, I do not mind to
hit the database again, (I do not want to cache the result in the
middle tier server, scalability issue), and I know that I could achieve
this with CURSOR, but unfortunately the FOR XML is not allowed in a
CURSOR statement .
(I know that I could achieve what I want to do by writing custom code
in the middle tier, but I just want to see if there is a way to do this
on the database side.)

Any comments & suggestion is greatly appreciated.
Thanks in advance.

(I am using SQL2005)


If you are on SQL 2005, I would suggest that you lool into the
row_number function. Here is a sample:

SELECT OrderID, OrderDate, CustomerID
FROM (SELECT OrderID, OrderDate, CustomerID,
rowno = row_number() OVER (ORDER BY OrderID)
FROM Northwind..Orders) AS a
WHERE rowno BETWEEN 100 AND 200
FOR XML AUTO

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 25 '06 #2

P: n/a
Check the following articles:

http://weblogs.asp.net/Firoz/archive...12/411949.aspx

http://sqljunkies.com/WebLog/amachan...1/03/4945.aspx

http://www.sqlmag.com/articles/index...ticleid=43922&

I use a tweaked version as shown in Itzik's article with sorting and
filtering. The only caveat is that is uses dynamic sql. With SQL 2005
you have a few more options to manage permissions for these type of
stored procedures. SQL Server MVP Erland has a very good article on
this topic:

http://www.sommarskog.se/grantperm.html

HTH.

Apr 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.