对sql for xml的查询结果做分页处理思路

对sql for xml的查询结果作分页处理思路

背景：在批量处理大量数据时，可能最终生成的xml文件中包含多条记录，客户希望作分页处理，让系统每次运行时自动生成多个xml文件。

提示：这里只是演示效果，已把真实的逻辑部分简化成单表，单一xml格式了。但我们在复杂情况下可以参考其做法。

效果图：

sql 代码：（运行环境：sql 08 R2, sql 2012）

 
-- sample  
-- select * from
--(
--	select *,ROW_NUMBER() over(order by [provider_id]) AS RowNum 
--	from  [Provider]
--) t where t.RowNum>=1 and t.RowNum<=10

use casenet

declare @external_id varchar(8)='0076283' -- here to be configured	
declare @PageSize int=5   -- here to be configured

declare @PageIndex int=1   -- start from 1, will be auto changed in dynamic running
declare @PageCount int  -- will be set value in dynamic running
declare @RecordCount int  -- will be set value in dynamic running

declare @xml xml  
declare @schemaVersion varchar(10)='5.1'  

select @RecordCount=max(RowNum)
from
(
	select PROVIDER_ID,ROW_NUMBER() over(order by [provider_id]) AS RowNum 
	from [Provider] p
	inner join integration.dbo.entity_mappings m1
		on p.provider_id= m1.internal_id and m1.type='Provider'
	--where p.external_id like '0189026A%'
			--or p.external_id like '0151722A%'
	where p.external_id like ''+ @external_id+ '%'
) a

select @PageCount=@RecordCount / @PageSize +  convert(int,convert(bit, @RecordCount % @PageSize ))
select @RecordCount as RecordCount,@PageSize as PageSize,@PageCount as [PageCount]

-- sample
--while(@PageIndex<=@PageCount)
--begin
--	select @PageIndex
--	select @PageIndex=@PageIndex+1
--end

while(@PageIndex<=@PageCount)
begin
	 
	set @xml=(
		select provider_id as provider_id
				,obsolete as obsolete
				,npi as npi
		from [Provider]
		WHERE provider_id IN
		(
			SELECT provider_id
			FROM
			(
				 select PROVIDER_ID,RowNum from
				(
					select PROVIDER_ID,ROW_NUMBER() over(order by [provider_id]) AS RowNum 
					from [Provider]
				) t where 
					--t.RowNum>=1 and t.RowNum<=10
					t.RowNum>= ( @PageSize * (@PageIndex-1) +1 )
					 and t.RowNum<=( @PageSize * @PageIndex )
			) A
		)  
		FOR XML PATH('provider'),ROOT('providers'),ELEMENTS XSINIL
	)
	
	set @xml.modify('insert attribute schemaVersion{sql:variable(''@schemaVersion'')} as last into (/providers)[1]')   --add property:schemaVersion    
	select @xml  
	
	select @PageIndex=@PageIndex+1

end

对sql for xml的查询结果做分页处理思路

相关推荐