title: Call webservice from sql stored procedure
body: <p>I want to call a webservice from my stored procedure and get an XML answere. Im don't realy know how to do this since i haven't done anything like this before. </p>
  
  <p>This is what my stored procedure looks like: </p>
  
  <pre><code>   USE [master]
  GO
  /****** Object:  StoredProcedure [dbo].[webServiceCall]    Script Date: 3/11/2014     9:12:49 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:      &lt;Author,,Name&gt;
  -- Create date: &lt;Create Date,,&gt;
  -- Description: &lt;Description,,&gt;
  -- =============================================
  ALTER PROCEDURE [dbo].[webServiceCall] 
      -- Add the parameters for the stored procedure here
      @personnr varchar (20),
      @username varchar (20),
      @password varchar (20)
  
  AS
   DECLARE @obj INT
   DECLARE @ValorDeRegreso INT
   DECLARE @hr INT
   DECLARE @src varchar(255)
   DECLARE @desc varchar (255)
   DECLARE @srv varchar (200)
   DECLARE @response varchar (8000)
  
  
  
  SET @srv = 'http://..../nasherpopman/PopManWebService.asmx?op=GetPerson&amp;personnumber=' + @personnr +'&amp;username=' + @username + '&amp;password=' + @password +''
  EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
  EXEC sp_OAMethod @obj, 'open', NULL, 'POST', @srv, false
  EXEC sp_OAMethod @obj, 'send'
  EXEC sp_OAGetProperty @obj, 'responseText', @response out
  SELECT @response [response]
  EXEC sp_OADestroy @obj
  RETURN
  </code></pre>
  
  <p>For now i get this error: Server was unable to process request. ---&gt; Root element is missing</p>
  
  <p>I just don't know what to do from this or what i am doing wrong here. </p>
  
  <p>If i try to run the url i get this:</p>
  
  <pre><code>GetPerson
  
  Test
  
  The test form is only available for requests from the local machine.
  SOAP 1.1
  
  The following is a sample SOAP 1.1 request and response. The placeholders shown need to     be replaced with actual values.
  
  POST /nasherpopman/PopManWebService.asmx HTTP/1.1
  Host: .....
  Content-Type: text/xml; charset=utf-8
  Content-Length: length
  SOAPAction: "http://tempuri.org/GetPerson"
  
  &lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"&gt;
    &lt;soap:Body&gt;
      &lt;GetPerson xmlns="http://tempuri.org/"&gt;
        &lt;personnumber&gt;string&lt;/personnumber&gt;
        &lt;username&gt;string&lt;/username&gt;
        &lt;password&gt;string&lt;/password&gt;
      &lt;/GetPerson&gt;
    &lt;/soap:Body&gt;
  &lt;/soap:Envelope&gt;
  HTTP/1.1 200 OK
  Content-Type: text/xml; charset=utf-8
  Content-Length: length
  
  &lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"&gt;
    &lt;soap:Body&gt;
      &lt;GetPersonResponse xmlns="http://tempuri.org/"&gt;
        &lt;GetPersonResult&gt;
  
        &lt;personResult&gt;string&lt;/personResult&gt;
      &lt;/GetPersonResponse&gt;
    &lt;/soap:Body&gt;
  &lt;/soap:Envelope&gt;
  </code></pre>