CODEWITHSUNDEEP
jsonsql-server
stefan_77
stefan_77

Reputation: 3

Parse JSON from web in SQL Server 2017

Is it possible to consume a JSON response from the web in SQL Server 2017 and then parse. I am interested in getting data from the public Open Sky API. I tried this but I just get NULL in @ResponseText :

Declare @Object as Int;
Declare @ResponseText as 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'
 Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT


 Select @ResponseText

 Exec sp_OADestroy @Object

Thanks for your help!

Upvotes: 0

Views: 3278

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32230

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.

Upvotes: 3

Related Questions