Help on json extract from API from TSQL

Question

Hi

I am trying to download data from an external web based application into my SQL Server through TSQL( as a first preference over SSIS/ Powershell) ).
I am very new to API and have been using the script provided by :
https://www.zealousweb.com/calling-rest-api-from-sql-server-stored-procedure/

I have generated an OAuth access token key for my application , and marked it in my url

However I am not able to see any output.
The @ResponseText (variable marked in the script from the above referred url) is returning blank when declaring @ResponseText as nVarchar(4000);
The @ResponseText is returning null when declaring @ResponseText as nVarchar(max);

DECLARE @ret INT;  
DECLARE @URL NVARCHAR(MAX) = 'https://<the website>.com/api/v1/me';  
Declare @Object as Int;  
Declare @ResponseText as nVarchar(4000);  
  
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;  
Exec sp_OAMethod @Object, 'open', NULL, 'get',  
       @URL,  
       'False'  
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'authentication', 'OAuth <accesstoken>'  
Exec sp_OAMethod @Object, 'send'  
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT  
Select @ResponseText  
  
  
exec  @ret=sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT  
SELECT @ret  





  
  
  
  

Answer 1

You select @ret, that's the numeric return code of the stored procedure sp_OAMethod: 0 = succes, otherwise a failure.
The API call result should be in variable @ResponseText

select @ResponseText