title: SOAP from TSQL in SQL Server
body: <p>I like to get some info from this web service: <a href="http://www.mnb.hu/arfolyamok.asmx?wsdl" rel="nofollow noreferrer">http://www.mnb.hu/arfolyamok.asmx?wsdl</a></p>
  
  <p>The info I need is the result of the <code>GetCurrentExchangeRates</code> method.</p>
  
  <p>I test it with SoapUI, with this request:</p>
  
  <pre><code>&lt;soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://www.mnb.hu/webservices/"&gt;
     &lt;soapenv:Header/&gt;
     &lt;soapenv:Body&gt;
        &lt;web:GetCurrentExchangeRates/&gt;
     &lt;/soapenv:Body&gt;
  &lt;/soapenv:Envelope&gt;
  </code></pre>
  
  <p>I got the correct response:</p>
  
  <pre><code>&lt;s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"&gt;
     &lt;s:Body&gt;
        &lt;GetCurrentExchangeRatesResponse xmlns="http://www.mnb.hu/webservices/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
           &lt;GetCurrentExchangeRatesResult&gt;&lt;![CDATA[&lt;MNBCurrentExchangeRates&gt;&lt;Day date="2017-01-06"&gt;&lt;Rate unit="1" curr="AUD"&gt;212,76&lt;/Rate&gt;&lt;Rate unit="1" curr="BGN"&gt;157,05&lt;/Rate&gt;&lt;Rate unit="1" curr="BRL"&gt;90,57&lt;/Rate&gt;&lt;Rate unit="1" curr="CAD"&gt;218,75&lt;/Rate&gt;&lt;Rate unit="1" curr="CHF"&gt;286,65&lt;/Rate&gt;&lt;Rate unit="1" curr="CNY"&gt;41,83&lt;/Rate&gt;&lt;Rate unit="1" curr="CZK"&gt;11,37&lt;/Rate&gt;&lt;Rate unit="1" curr="DKK"&gt;41,32&lt;/Rate&gt;&lt;Rate unit="1" curr="EUR"&gt;307,16&lt;/Rate&gt;&lt;Rate unit="1" curr="GBP"&gt;358,91&lt;/Rate&gt;&lt;Rate unit="1" curr="HKD"&gt;37,36&lt;/Rate&gt;&lt;Rate unit="1" curr="HRK"&gt;40,53&lt;/Rate&gt;&lt;Rate unit="100" curr="IDR"&gt;2,17&lt;/Rate&gt;&lt;Rate unit="1" curr="ILS"&gt;75,42&lt;/Rate&gt;&lt;Rate unit="1" curr="INR"&gt;4,26&lt;/Rate&gt;&lt;Rate unit="1" curr="ISK"&gt;2,56&lt;/Rate&gt;&lt;Rate unit="100" curr="JPY"&gt;249,83&lt;/Rate&gt;&lt;Rate unit="100" curr="KRW"&gt;24,27&lt;/Rate&gt;&lt;Rate unit="1" curr="MXN"&gt;13,58&lt;/Rate&gt;&lt;Rate unit="1" curr="MYR"&gt;64,78&lt;/Rate&gt;&lt;Rate unit="1" curr="NOK"&gt;34,12&lt;/Rate&gt;&lt;Rate unit="1" curr="NZD"&gt;203,58&lt;/Rate&gt;&lt;Rate unit="1" curr="PHP"&gt;5,87&lt;/Rate&gt;&lt;Rate unit="1" curr="PLN"&gt;70,45&lt;/Rate&gt;&lt;Rate unit="1" curr="RON"&gt;68,17&lt;/Rate&gt;&lt;Rate unit="1" curr="RSD"&gt;2,48&lt;/Rate&gt;&lt;Rate unit="1" curr="RUB"&gt;4,88&lt;/Rate&gt;&lt;Rate unit="1" curr="SEK"&gt;32,16&lt;/Rate&gt;&lt;Rate unit="1" curr="SGD"&gt;202,09&lt;/Rate&gt;&lt;Rate unit="1" curr="THB"&gt;8,12&lt;/Rate&gt;&lt;Rate unit="1" curr="TRY"&gt;80,08&lt;/Rate&gt;&lt;Rate unit="1" curr="UAH"&gt;10,74&lt;/Rate&gt;&lt;Rate unit="1" curr="USD"&gt;289,75&lt;/Rate&gt;&lt;Rate unit="1" curr="ZAR"&gt;21,26&lt;/Rate&gt;&lt;/Day&gt;&lt;/MNBCurrentExchangeRates&gt;]]&gt;&lt;/GetCurrentExchangeRatesResult&gt;
        &lt;/GetCurrentExchangeRatesResponse&gt;
     &lt;/s:Body&gt;
  &lt;/s:Envelope&gt;
  </code></pre>
  
  <p>I try to read this information from SQL Server this way (I create a procedure for it, see the code below)</p>
  
  <pre><code>CREATE proc [dbo].[spHTTPRequest] 
        @URI varchar(2000) = '',      
        @methodName varchar(50) = '', 
        @requestBody varchar(8000) = '', 
        @SoapAction varchar(255), 
        @UserName nvarchar(100), -- Domain\UserName or UserName 
        @Password nvarchar(100), 
        @responseText varchar(8000) output
  AS
      SET NOCOUNT ON
  
      IF @methodName = ''
      BEGIN
        select FailPoint = 'Method Name must be set'
        return
      END
  
      SET @responseText = 'FAILED'
  
      DECLARE @objectID int
      DECLARE @hResult int
      DECLARE @source varchar(255), @desc varchar(255) 
  EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
  IF @hResult &lt;&gt; 0 
  BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
        SELECT      hResult = convert(varbinary(4), @hResult), 
                    source = @source, 
                    description = @desc, 
                    FailPoint = 'Create failed', 
                    MedthodName = @methodName 
        goto destroy 
        return
  END
  -- open the destination URI with Specified method 
  EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
  IF @hResult &lt;&gt; 0 
  BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
        SELECT      hResult = convert(varbinary(4), @hResult), 
              source = @source, 
              description = @desc, 
              FailPoint = 'Open failed', 
              MedthodName = @methodName 
        goto destroy 
        return
  END
  -- set request headers 
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
  IF @hResult &lt;&gt; 0 
  BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
        SELECT      hResult = convert(varbinary(4), @hResult), 
              source = @source, 
              description = @desc, 
              FailPoint = 'SetRequestHeader failed', 
              MedthodName = @methodName 
        goto destroy 
        return
  END
  -- set soap action 
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction 
  IF @hResult &lt;&gt; 0 
  BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
        SELECT      hResult = convert(varbinary(4), @hResult), 
              source = @source, 
              description = @desc, 
              FailPoint = 'SetRequestHeader failed', 
              MedthodName = @methodName 
        goto destroy 
        return
  END
  declare @len int
  set @len = len(@requestBody) 
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len 
  IF @hResult &lt;&gt; 0 
  BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
        SELECT      hResult = convert(varbinary(4), @hResult), 
              source = @source, 
              description = @desc, 
              FailPoint = 'SetRequestHeader failed', 
              MedthodName = @methodName 
        goto destroy 
        return
  END
  
  -- send the request 
  EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody 
  IF    @hResult &lt;&gt; 0 
  BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
        SELECT      hResult = convert(varbinary(4), @hResult), 
              source = @source, 
              description = @desc, 
              FailPoint = 'Send failed', 
              MedthodName = @methodName 
        goto destroy 
        return
  END
  declare @statusText varchar(1000), @status varchar(1000) 
  -- Get status text 
  exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
  exec sp_OAGetProperty @objectID, 'Status', @status out
  select @status, @statusText, @methodName 
  -- Get response text 
  exec sp_OAGetProperty @objectID, 'responseText', @responseText out
  IF @hResult &lt;&gt; 0 
  BEGIN
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
        SELECT      hResult = convert(varbinary(4), @hResult), 
              source = @source, 
              description = @desc, 
              FailPoint = 'ResponseText failed', 
              MedthodName = @methodName 
        goto destroy 
        return
  END
  destroy: 
        exec sp_OADestroy @objectID 
  SET NOCOUNT OFF
  
  GO
  */
  
  declare @xmlOut varchar(8000)
  Declare @RequestText as varchar(8000);
  set @RequestText=
  '&lt;soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://www.mnb.hu/webservices/"&gt;
      &lt;soapenv:Header/&gt;
      &lt;soapenv:Body&gt;
          &lt;web:GetCurrentExchangeRates/&gt;
      &lt;/soapenv:Body&gt;
  &lt;/soapenv:Envelope&gt;'
  
  exec spHTTPRequest 'http://www.mnb.hu/arfolyamok.asmx?wsdl', 'post', @RequestText,'http://tempuri.org/CreateOrderForMe','', '', @xmlOut out
  </code></pre>
  
  <p>But I always get an "Internal server" error. Can somebody suggest me a solution?</p>
  
  <p>Thanks!</p>