用存储过程生成xml文件

最新推荐文章于 2021-04-06 14:35:50 发布

kikicat0226

最新推荐文章于 2021-04-06 14:35:50 发布

阅读量307

点赞数

文章标签：存储 xml insert table float

本文链接：https://blog.csdn.net/kikicat0226/article/details/2730265

版权

CREATE proc SP_SaveXML


@fname varchar(1000)="d:/line.xml"

as
set nocount on
declare @startime datetime    
declare @nWorth float 		
declare @i int			


set @startime=(select top 1 nTime from Test_data order by nTime)
set @nWorth=(select top 1 nWorth from Test_Data order by nTime)		
set @i=0

create table ##t(re nvarchar(4000))
insert ##t
select Re='
 
 '
union all select '
 
 
  
  '
union all select '
  
  
   
   '

while datediff(day,@startime,getdate())>=0
begin
insert ##t
select '
   
   
    
    '+cast(CONVERT(varchar(12),@startime,102) as varchar)+'
   
   '
set @i=@i+1
set @startime=DATEADD(day,1,@startime)
end

insert ##t
select '
  
  '
union all select '
  
  
   
   '
union all select '
   
   
    
    '


set @startime=(select top 1 nTime from Test_data order by nTime)	
set @i=0

while datediff(day,@startime,getdate())>=0

begin

if exists(select top 1 nWorth from Test_Data where nTime=@startime) 

set @nWorth=(select top 1 nWorth from Test_Data where nTime=@startime)

insert ##t
select '
    
    
     
     '+cast(cast(@nWorth as decimal(10,3)) as varchar)+'
    
    '

set @i=@i+1
set @startime=DATEADD(day,1,@startime)
end

insert ##t
select '
   
   '
union all select '
  
  '

union all select '
 
 '

exec('master..xp_cmdshell ''bcp ##t out "'+@fname+'" /P"" /c''')

drop table ##t
GO