set nocount on set dateformat ymd
declare @long float, @lat float, @direction int, @tele_time datetime, @speed int
declare @id int, @text1 varchar(1024),@ts_garaj varchar(30),@start_time datetime, @stop_time datetime, @wpl int, @track int, @ObjectID int
declare @sens_num int

select @wpl=1, @track=1 -- какие типы данных стоит выводить
select @sens_num=6 -- номер датчика
select @ts_garaj='Котовск тест', @start_time ='2013-08-02 06:00', @stop_time='2013-08-02 13:51' -- гаражный номер, период выгрузки
select @ObjectID = ObjectId from bn.dbo.FLEET_Object as O where o.ObjectGarNum like @ts_garaj and o.ObjectActive=1
create table #t (num int identity (1,1) not null,
string varchar(1024))

set @Text1 = N'<?xml version="1.0"?>'   
insert into #t (string)
values (@Text1)
set @Text1 = N'<gpx version="1.0" creator="bnc.ucoz.net" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.topografix.com/GPX/1/0" xsi:schemaLocation="http://www.topografix.com/GPX/1/0 http://www.topografix.com/GPX/1/0/gpx.xsd">'+
'<time>'+convert (varchar(30),@start_time,126)+'</time> <name>UpLoad</name> <desc>DownLoad from CyberFleet</desc>'
insert into #t (string)
values (@Text1)

if @wpl=1 begin -- выгружаем точки - стоянки

     -- экспортируем данные об остановках   
     declare cur_history cursor local read_only for -- обявляем курсор   
     --    select z.id, z.name,'' from bn_patp.dbo.PATP_BasePoint as Z      
     --    order by z.name -- в алфавитном порядке   
     select ad.long, ad.lat, ad.direction,ad.tele_time, ad.speed  from bn.dbo.SYS_DEV_ArchiveData as AD   
     where Ad.ObjectID=@ObjectID and tele_time between @start_time and @stop_time and valid=1
     and  digit_sens_num&power(2,@sens_num-1)>0
     order by tele_time;   
     ;   

        open cur_history   

     fetch next from cur_history into @long,@lat,@direction,@tele_time,@speed
     while @@fetch_status=0      
     begin   
        -- выборка данных об остановке.   
        set @Text1 = N'    <wpt lat="'+str(@lat,15,8)+N'" lon="'+str(@long,15,8)+N'">   
        <time>'+convert(varchar(30),@tele_time,126)+N'</time>   
        <name>'+convert(varchar(30),@tele_time,126)+N'</name>   
        <magvar>'+cast(@direction as varchar(3))+N'</magvar>   
     </wpt>';   
     insert into #t (string)
     values (@Text1)

     fetch next from cur_history into @long,@lat,@direction,@tele_time,@speed
     end -- open cur_zone while @@fetch_status=0 begin   

     close cur_history   
     deallocate cur_history   

end

if @track=1 begin -- выгружаем трек
     -- экспортируем данные об остановках   
     declare cur_history cursor local read_only for -- обявляем курсор   
     --    select z.id, z.name,'' from bn_patp.dbo.PATP_BasePoint as Z      
     --    order by z.name -- в алфавитном порядке   
     select ad.long, ad.lat, ad.direction,ad.tele_time, ad.speed  from bn.dbo.SYS_DEV_ArchiveData as AD   
     inner join bn.[dbo].[FLEET_Object] as T on t.ObjectID=ad.ObjectID
     where t.ObjectGarNum=@ts_garaj and t.ObjectActive=1 and tele_time between @start_time and @stop_time and valid=1
     --and speed<4
     order by tele_time;   
     ;   

     set @Text1 = N'<trk><trkseg>';   
     insert into #t (string)
     values (@Text1)

        open cur_history   

     fetch next from cur_history into @long,@lat,@direction,@tele_time,@speed
     while @@fetch_status=0      
     begin   
        -- выборка данных об остановке.   
        set @Text1 = '    <trkpt lat="'+replace(str(@lat,15,8),' ','')+'" lon="'+replace(str(@long,15,8),' ','')+'">   
        <time>'+convert(varchar(30),@tele_time,126)+'</time>   
        <name>'++cast(@speed as varchar(3))+'</name>   
        <magvar>'+cast(@direction as varchar(3))+'</magvar>   
     </trkpt>';   
     insert into #t (string)
     values (@Text1)

     fetch next from cur_history into @long,@lat,@direction,@tele_time,@speed
     end -- open cur_zone while @@fetch_status=0 begin   

     close cur_history   
     deallocate cur_history      
     set @Text1 = N'</trkseg></trk>';   
     insert into #t (string)
     values (@Text1)

end
      set @Text1 = N'</gpx>'   
insert into #t (string)
values (@Text1)

select string from #t order by num
drop table #t