背景: 在批量处理大量数据时,可能最终生成的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