set nocount on
DECLARE @hdoc int
DECLARE @x xml;
declare @str nvarchar(max), @str1 nvarchar(max), @zoneind int
SET @x = (SELECT * FROM OPENrowset(
BULK 'd:\1\1.kml',
SINGLE_BLOB) AS x);

--select @x

set @x=cast (replace (cast(@x as nvarchar(max)),'xmlns="http://www.opengis.net/kml/2.2"','') as xml)
set @zoneind=0
declare cur cursor for

select cast(@x.query ('/kml/Document/Folder/Placemark/Polygon/outerBoundaryIs/LinearRing/coordinates') as nvarchar(max))
create table #t (zonename varchar(30),ind int, lat float,long float)
declare @zonename varchar(30), @ind int, @lat float, @lon float
declare @i int, @razd int

open cur
while 1=1 begin
    fetch next from cur into @str
    if @@FETCH_STATUS!=0 break
    --print @str

    while len(@str)>0 begin
       select @razd=CHARINDEX (char(10),@str)
       if @razd>0
          select @str1=ltrim(rtrim(left(@str,CHARINDEX (char(10),@str)-1)))
       else  
          select @str1=@str

       --select  @str1
       if charindex('coordinates',@str1)>0 begin
       select @ind=0,@zoneind=@zoneind+1
       --select @zoneind
       end
       else begin
          -- это координаты
          --select @str1, 'ап'

          select @lon = left(@str1,(CHARINDEX (',',@str1)-1))
          select @str1= SUBSTRING (@str1,CHARINDEX (',',@str1)+1,len(@str1))
          --select @str1, 'ап'
          select @lat = left(@str1,(CHARINDEX (',',@str1)-1))
          select @ind=@ind+1
          insert into #t
          values ('Зона '+cast(@zoneind as varchar(3)),@ind,@lat,@lon)
          end

       if @razd>0   
          select @str=SUBSTRING (@str,CHARINDEX (char(10),@str)+1,len(@str))
       else select @str=''
    end  

end
close cur

deallocate cur

create table #str (num int IDENTITY(1,1) NOT NULL,string varchar(1000) not null)
insert into #str (string)
values ('set nocount on use bn ')

insert into #str (string)
select 'insert into FLEET_Zone ([Name],[Type],[Width],color,[GlobalType],zoneactive,[Integrated],[Comment],[IsTemporary],adddate,[ZoneIsDynamic],[ZoneMaxSpeed]) '+
'values ('''+zonename +''',1,2,0,1,1,0,''автосозданная зона'',0,getdate(),0,0)' from #t
group by zonename

insert into #str (string)
values ('--')

insert into #str (string)
select 'insert into [dbo].[FLEET_ZoneDescript] ([ZoneID],[Long],[Lat],[OrderBy]) '+
--''
'select [ZoneID]'+','+ltrim(str(long,12,8))+','+ltrim(str(lat,12,8))+','+cast(ind as varchar(3))+' from [dbo].[FLEET_Zone] where [Name]='''+zonename+''' and [ZoneActive]=1 '
from #t

drop table #t

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