create PROCEDURE [dbo].[zz_LoadXMLFromFile] 
      @tcFileName             VARCHAR(4000),    
      @xml_output             xml OUTPUT
AS
BEGIN  
      SET NOCOUNT ON;      
      DECLARE @Obj int 
      DECLARE @Result int 
      DECLARE @HTTPStatus int 


      EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 
 
      EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @tcFileName, false
      EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
      EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
      EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 

      declare @Tab table (x xml)

      insert into @tab
      EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'-- ,@XML output


      set @xml_output = (Select x from @tab) 
END

      declare @xml_String xml
      declare @url varchar(250)
      SET NOCOUNT ON; 

      select @url = '...'

      exec zz_LoadXMLFromFile  
                  @url, 
                  @xml_String output

      select @xml_String

EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'

begin try 

      insert into @tab
      EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'-- ,@XML output
end try
begin catch
    declare @err VARCHAR(4000)
    SELECT
        @err=
            'ERROR_NUMBER: '+convert(varchar(4000),ERROR_NUMBER())
            +'    ERROR_SEVERITY: '+convert(varchar(4000),ERROR_SEVERITY())
            +'    ERROR_STATE: '+convert(varchar(4000),ERROR_STATE())
            +'    ERROR_PROCEDURE: '+convert(varchar(4000),ERROR_PROCEDURE())
            +'    ERROR_LINE: '+convert(varchar(4000),ERROR_LINE())
            +'    ERROR_MESSAGE: '+ERROR_MESSAGE() 
        print @err
end catch

EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'

      create table #tab (x  NVARCHAR(max))
      insert into #tab
      EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--,@XML output
      select * from #tab

      create table #tab (x  xml)
      insert into #tab
      EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--,@XML output
      select * from #tab

      declare @xml_output             xml

      create table #tab (x  NVARCHAR(max))

      insert into #tab
      EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--,@XML output

      Select @xml_output=x from #tab
      select @xml_output