begin transaction;

        if not exists (select * from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null )
          insert TourML.SpoFiles( is_directory, name ) 
            select 1, @dir 
              where not exists (select * from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null );
        
        declare @id table( stream_id uniqueidentifier );
        insert TourML.SpoFiles( name, file_stream ) 
          output inserted.stream_id into @id(stream_id)
          select cast( newid() as nvarchar(255) ), cast( @XML as varbinary(max) );

        with fo as ( select path_locator from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null )
           , fl as ( select f.* from TourML.SpoFiles as f inner join @id as t on t.stream_id = f.stream_id where f.is_directory = 0 )
           update fl set path_locator = fl.path_locator.GetReparentedValue(hierarchyid::GetRoot(), fo.path_locator), name = @name
              from fl cross join fo;

commit transaction;

      begin transaction;

        if not exists (select * from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null )
          insert TourML.SpoFiles( is_directory, name ) 
            select 1, @dir 
              where not exists (select * from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null );
        
        with f  as ( select * from TourML.SpoFiles )
           , fl as ( select * from f where is_directory = 0 and file_type = N'xml' and name = @name )
           , fo as ( select * from f where is_directory = 1 and name = @dir and parent_path_locator is null )
           , x  as ( select path_locator = isnull( ( select path_locator from fl where parent_path_locator = fo.path_locator )
                                                 , fo.path_locator.GetDescendant( (select max(path_locator) from f where parent_path_locator = fo.path_locator ), null)
                                                 )
                          , name = @name
                          , file_stream = cast( @XML as varbinary(max) )
                          , creation_time = @now
                       from fo
                   )
          merge f using x on f.path_locator = x.path_locator
            when not matched then insert ( path_locator, name, file_stream, creation_time ) values( path_locator, name, file_stream, creation_time )
            when matched and ( f.file_stream <> x.file_stream ) then update set file_stream = x.file_stream, creation_time = @now
       ;
       
      commit transaction;