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
--Insert Into @StoreList Stores within the radius selected
,Cast((GeoLocation.STDistance(@GeoLocation)/1609.344) as numeric(4,2)) as Distance
From dbo.Stores s
Where GeoLocation.STDistance(@GeoLocation)<=(@Radius * 1609.344) and Active = 1
Order By Distance
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)
drop table #RadiusStoreList