Solved

stored proc paging with XML SQL 2008

Posted on 2011-09-12
5
376 Views
Last Modified: 2012-05-12
XML Newbie needs help with paging.  I have the below query that return XML results for all locations in a specific radius. Xml results are required due to the 1 to many relationship on the features for a location.  Now I have been asked do paging.  

The example I found here: http://www.mssqltips.com/tip.asp?tip=1699 looks very simple, but not so much with XML results( at least for me ).  I prefer to use the CTE example due to performance, but am finding it difficult to get there with the XML.

Let's say I want to display 20 records per page and want to return results for page 2.  I have 41 records total in the resultset.  So I want to display records 21 through 40 only.  I am populating @xmlData with the top 40 rows based on the order of Distance.

 

I'm pulling the ROW_NUMBER in as the second field but can't figure out how to filter out the first 20 Row Numbers and only return 21 through 40.  You can't use Row_Number in the where clause and that seems to be where I'm stuck.  

Is there a way to get the correct record numbers returned and still return the results with the same XML structure without a whole rewrite?  This comes from a very complex stored procedure and I would prefer not to have to rewrite the whole thing, if possible.

Any insight is appreciated!
DECLARE @GPSLatitude numeric(9,6)
DECLARE @GPSLongitude numeric(9,6)
DECLARE @GeoLocation geography
DECLARE @Radius int
DECLARE @xmlData xml
DECLARE @Page int
DECLARE @RecsPerPage int = NULL				--Number of records per page  
											--		0 = ALL RECORDS 
											--		If nothing passed, default will be 20 records returned per page
DECLARE @FirstRec int
DECLARE @LastRec int

SET @Radius = 30
SET @RecsPerPage = 20
SET @Page = 2
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

		
-----------------------------------------------------------


EXEC dbo.usp_GeocodeSearch 'Winston-Salem', 'NC',@GPSLatitude = @GPSLatitude OUTPUT,@GPSLongitude = @GPSLongitude OUTPUT

			IF @GPSLatitude IS NOT NULL and @GPSLongitude IS NOT NULL
				Set @GeoLocation = geography::STPointFromText('POINT(' + CAST(@GPSLongitude AS VARCHAR(20)) + ' ' + CAST(@GPSLatitude AS VARCHAR(20)) + ')', 4326)
			
			IF @GeoLocation IS NOT NULL
				BEGIN
					--Insert Into @StoreList Stores within the radius selected
					Select StoreId
						  ,Cast((GeoLocation.STDistance(@GeoLocation)/1609.344) as numeric(4,2)) as Distance
					Into #RadiusStoreList
					From dbo.Stores s
					Where GeoLocation.STDistance(@GeoLocation)<=(@Radius * 1609.344) and Active = 1
					Order By Distance		
				END


Select @xmlData = (Select Top (@RecsPerPage)  
					s.StoreId as "@ID"
					,ROW_NUMBER() OVER(ORDER BY  Distance ) as "RowNum"
					,s.Address1 as "Address/Address1"
					,s.Address2 as "Address/Address2"
					,s.City as "Address/City"
					,st.StateCode as "Address/StateCode"
					,st.StateName as "Address/StateName"
					,s.PostalCode as "Address/PostalCode"
					,s.PhoneNumber as "Address/PhoneNumber"
					,s.MapURL as "Address/MapURL"
					,( Select    f.FeatureName as "@Name"
								,f.IconInfo as "@Icon"
								,f.IsRestaurant as "@IsRestaurant"
						 From dbo.Feature f
						 Inner Join dbo.StoreFeatureXref x  on
							 f.FeatureId = x.FeatureId 
						 Where x.StoreId = s.StoreId  
						 Order by f.FeatureName
					     for xml path ('Feature'), type) as "Features"
					  ,stat.StatusDescription as "Status" ,sl.Distance as "Search/Distance"  From dbo.Stores s
					Inner Join dbo.[State] st on
						s.StateId = st.StateId
					Left Outer Join dbo.[Status] stat on
						s.StoreId = stat.StoreId  
					Inner Join #RadiusStoreList sl on
						s.StoreId = sl.StoreId  
					Where s.Active = 1  
						and s.StoreId in (Select wx.StoreId 
														  From dbo.Feature wf
														  Inner Join dbo.StoreFeatureXref wx  on
															 wf.FeatureId = wx.FeatureId 
														  Where wf.FeatureName in ('ATM')) 
					Order by  Distance 
					FOR XML PATH('Store'), root('Stores'), ELEMENTS XSINIL, type)
	
	
select @xmlData	

drop table #RadiusStoreList

Open in new window

0
Comment
Question by:edfreels
  • 3
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36523936
Put the data portion  of the query with ROW_NUMBER() calculation in the CTE and then apply the FOR XML PATH in the final selection. That way you can use RN to do the paging you desire.
0
 
LVL 1

Author Comment

by:edfreels
ID: 36523996
If I'm understanding you correctly, you are saying to run the query and return table data first and then format the xml in a second query?  If that's what you mean, that would cause a whole rewrite of the stored proc.  Much of the query is built dynamically based on parameters passed and then executed with EXEC at the end of the stored proc.  If I misunderstood, can you give me an example of what you mean?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36524125
I was focusing on this piece:
Select @xmlData = (Select Top (@RecsPerPage)  
	s.StoreId as "@ID"
	,ROW_NUMBER() OVER(ORDER BY  Distance ) as "RowNum"
	,s.Address1 as "Address/Address1"
	,s.Address2 as "Address/Address2"
	,s.City as "Address/City"
	,st.StateCode as "Address/StateCode"
	,st.StateName as "Address/StateName"
	,s.PostalCode as "Address/PostalCode"
	,s.PhoneNumber as "Address/PhoneNumber"
	,s.MapURL as "Address/MapURL"
	,( Select    f.FeatureName as "@Name"
				,f.IconInfo as "@Icon"
				,f.IsRestaurant as "@IsRestaurant"
			From dbo.Feature f
			Inner Join dbo.StoreFeatureXref x  on
				f.FeatureId = x.FeatureId 
			Where x.StoreId = s.StoreId  
			Order by f.FeatureName
			for xml path ('Feature'), type) as "Features"
		,stat.StatusDescription as "Status" ,sl.Distance as "Search/Distance"  From dbo.Stores s
	Inner Join dbo.[State] st on
		s.StateId = st.StateId
	Left Outer Join dbo.[Status] stat on
		s.StoreId = stat.StoreId  
	Inner Join #RadiusStoreList sl on
		s.StoreId = sl.StoreId  
	Where s.Active = 1  
		and s.StoreId in (Select wx.StoreId 
					From dbo.Feature wf
					Inner Join dbo.StoreFeatureXref wx  on
					wf.FeatureId = wx.FeatureId 
					Where wf.FeatureName in ('ATM')) 
	Order by  Distance 
	FOR XML PATH('Store'), root('Stores'), ELEMENTS XSINIL, type)

Open in new window


I didn't see any dynamic SQL or parameters in that. In fact, you can probably have that be a view and then just select from the view WHERE RowNum BETWEEN x AND y, right?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36524142
I see now the #RadiusStoreList is in the joined tables... Hmm.
See if the below works better.
Select @xmlData = (SELECT * /* replace this with colum list as needed */
FROM (Select 
	s.StoreId as "@ID"
	,ROW_NUMBER() OVER(ORDER BY  Distance ) as "RowNum"
	,s.Address1 as "Address/Address1"
	,s.Address2 as "Address/Address2"
	,s.City as "Address/City"
	,st.StateCode as "Address/StateCode"
	,st.StateName as "Address/StateName"
	,s.PostalCode as "Address/PostalCode"
	,s.PhoneNumber as "Address/PhoneNumber"
	,s.MapURL as "Address/MapURL"
	,( Select    f.FeatureName as "@Name"
				,f.IconInfo as "@Icon"
				,f.IsRestaurant as "@IsRestaurant"
			From dbo.Feature f
			Inner Join dbo.StoreFeatureXref x  on
				f.FeatureId = x.FeatureId 
			Where x.StoreId = s.StoreId  
			Order by f.FeatureName
			for xml path ('Feature'), type) as "Features"
		,stat.StatusDescription as "Status" ,sl.Distance as "Search/Distance"  From dbo.Stores s
	Inner Join dbo.[State] st on
		s.StateId = st.StateId
	Left Outer Join dbo.[Status] stat on
		s.StoreId = stat.StoreId  
	Inner Join #RadiusStoreList sl on
		s.StoreId = sl.StoreId  
	Where s.Active = 1  
		and s.StoreId in (Select wx.StoreId 
					From dbo.Feature wf
					Inner Join dbo.StoreFeatureXref wx  on
					wf.FeatureId = wx.FeatureId 
					Where wf.FeatureName in ('ATM')) 
) derived
WHERE RowNum BETWEEN 21 AND 40
FOR XML PATH('Store'), root('Stores'), ELEMENTS XSINIL, type)

Open in new window

0
 
LVL 1

Author Comment

by:edfreels
ID: 36524227
That worked!  Thank you!  I had tried that but put the FOR XML within the derived section rather than outside of it.  I knew it had to be easy and you got me there!  Thank you again!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Granting access to Microsoft SQL Server 17 28
XSLT Help 12 21
SQL Server can be started but not accessed 1 17
select over clause 1 10
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now