OK, I do have an SQL Server 2008 instance with OLE Automation enabled, so I can help with that but not so much with the JSON parsing.
First, pretty much everybody universally says, "Use SQLCLR to do this and not OLE Automation." You should look at using SQLCLR for a new project.
Second, here's how I figured it out. First, all the sp_OA* stored procedures return a result code. We need to see that:
Declare @Object as Int;
Declare @ResponseText as nvarchar(max);
Declare @hr int;
Exec @hr = sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
select @hr;
Exec @hr = sp_OAMethod @Object, 'open', NULL, 'get',
'https://opensky-network.org/api/states/all',
'false';
select @hr;
Exec @hr = sp_OAMethod @Object, 'send';
select @hr;
Exec @hr = sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT;
select @hr;
Exec @hr = sp_OADestroy @Object
select @hr
Running that returned 0, 0, 0, -2147211494, 0. So the next to last call is the error. The number translates to an HRESULT of 0x8004271A (thank you calc.exe, although you can also use PowerShell: '{0:x}' -f -2147211494), but that didn't lead me anywhere. However, it does tell us where to use sp_OAGetErrorInfo:
Declare @Object as Int;
Declare @ResponseText as nvarchar(max);
Declare @source nvarchar(255), @description nvarchar(255)
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
'https://opensky-network.org/api/states/all',
'false';
Exec sp_OAMethod @Object, 'send';
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT;
Exec sp_OAGetErrorInfo @Object, @Source OUT, @Description OUT
Select @source, @description;
Exec sp_OADestroy @Object
Which returned the error Error in srv_convert.. That error message led me here, which said you had to use a table and not a variable for the output if it was a very long string for some unspecified reason.
That gives me code that works:
Declare @Object as Int;
Declare @Response table (txt nvarchar(max));
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
'https://opensky-network.org/api/states/all',
'false';
Exec sp_OAMethod @Object, 'send';
Insert Into @Response (txt)
Exec sp_OAMethod @Object, 'responseText'
Exec sp_OADestroy @Object
Select txt From @Response
SQL Server 2016+ comes with JSON parsing functions which have a similar syntax to the older XML functions and are about as easy to use. (Which is to say: not very easy until you're familiar with them.)
- Use the JSON_VALUE function to extract a scalar value from a JSON string.
- Use JSON_QUERY to extract an object or an array from a JSON string.
- Use the ISJSON function to test whether a string contains valid JSON.
- Use the JSON_MODIFY function to change a value in a JSON string.
There's also the OPENJSON rowset function.
