Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

stored proc paging with XML SQL 2008

Posted on 2011-09-12
5
Medium Priority
?
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 60

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 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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

Implementing Azure Infrastructure Exam 70-533

This course is designed to familiarize and instruct students in the content that is covered by Microsoft Exam 70-533, Implementing Microsoft Azure Solutions. It focuses on all the November 2016 objective domain topics.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 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