title: SSMS: import data from REST call
body: <p>I want to see if I can import data to MSSQL from a REST call to a URL. I would like to reuse this code for an hypothetic stored procedure in the future. This just wants so be a proof of concept.</p>
  
  <p>This URL has some JSON that I can use for my experiment:</p>
  
  <p><a href="http://api.plos.org/search?q=title:DNA" rel="nofollow noreferrer">http://api.plos.org/search?q=title:DNA</a></p>
  
  <p>I check if the URL returns some JSON with Postman:</p>
  
  <p><a href="https://i.stack.imgur.com/Prx8B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Prx8B.png" alt="enter image description here"></a>
  All good.</p>
  
  <p>I now follow <a href="https://stackoverflow.com/questions/22067593/calling-an-api-from-sql-server-stored-procedure">this guide</a> that tells me how to structure the call and this is my code:</p>
  
  <pre><code>DECLARE @Object AS INT;
  DECLARE @ResponseText AS nvarchar(max);
  
  EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
  EXEC sp_OAMethod @Object, 'open', NULL, 'GET','http://api.plos.org/search?q=title:DNA', 'false'
  EXEC sp_OAMethod @Object, 'send'
  EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
  SELECT @ResponseText
  EXEC sp_OADestroy @Object
  </code></pre>
  
  <p>But unfortunately it returns me a <code>NULL</code> column.</p>
  
  <p><a href="https://i.stack.imgur.com/Lcrfg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Lcrfg.png" alt="enter image description here"></a></p>
  
  <p>What am I doing wrong? </p>
  
  <p>Is there anyone out there which has ever arrived to make a REST call through SSMS?</p>