Ways to use REST API output of JSON Data and import in SQL Server:
JSON stands for JavaScript Object Notation. JSON is a lightweight format for storing and transporting data.
Let us take a real world scenario. There are plenty of REST API calls are available. A RESTful API — also referred to as a RESTful web service or REST API — is based on representational state transfer (REST). Usually they provide the useful data like Weather statistics, Exchange Rates, Cryptocurrency rates, Universities present in a location etc.,
https://api.exchangerate-api.com/v4/latest/INR-> API to get exchange rates
https://api.coinpaprika.com/v1/coins/btc-bitcoin -> API to get bitcoin details
If we look out the output of the REST API calls, usually they are in the format of JSON.
For Example, https://api.exchangerate-api.com/v4/latest/INR
Here we can see that this API call provides result as JSON data . i.e. for INR (Indian Currency for Rs 1), other country currency values like USD, GBP, etc., are provided with their equivalent values for the current date (i.e. this data shows as on June 1, 2021)
So like this we can get JSON output from any REST API calls.
Let us see, how we can use them in SQL Server
I am using Azure Data Studio for connecting SQL Server.
It can be downloaded from
Code Snippet for getting JSON response and use in SQL Server
In order to communicate any JSON API from the SQL Server, we require OLE Automation Stored Procedures. By default they are disabled by default.
First enable them
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO
EXEC sp_configure ‘Ole Automation Procedures’, 1;
RECONFIGURE;
GO
— an instance of the WinHTTP object is created with the help of the sp_OACreate procedure.
Declare @WinHttpObject as Int;
Declare @ResponseJsonText as Varchar(8000);
Exec sp_OACreate ‘WinHttp.WinHttpRequest.5.1’, @WinHttpObject OUT;
— sp_OAMethod procedure opened an HTTP connection , here it is https://api.exchangerate-api.com/v4/latest/INR
Exec sp_OAMethod @WinHttpObject, ‘open’, NULL, ‘get’,
‘https://api.exchangerate-api.com/v4/latest/INR'
Exec sp_OAMethod @WinHttpObject, ‘send’
— ResponseText method retrieved the response of the web server as a text.
Exec sp_OAMethod @WinHttpObject, ‘responseText’, @ResponseJsonText OUTPUT
— sp_OADestroy procedure destroyed the created instance of the object.
Exec sp_OADestroy @WinHttpObject
— checking for the validity of JSON
IF ISJSON(@ResponseJsonText)=1
— Get the necessary details
BEGIN
SELECT INRRates,USDRates,GBPRates FROM OPENJSON(@ResponseJsonText)
WITH(INRRates VARCHAR(10) ‘$.rates.INR’,USDRates VARCHAR(10) ‘$.rates.USD’,GBPRates VARCHAR(10) ‘$.rates.GBP’)
END
Output Screenshot:
We have the option of storing the data in JSON format from results window
Once the data is stored, we can parse the data and import into SQL Server very easily.
/*Way to import the JSON data into SQL Server
Provide the json file present in your local system(with full path)
The first step is to load the JSON file content in a table. We can use the table value function OPENROWSET for reading data from a file and return a table in the output. This table contains a single column and loads entire file data into it.
- SINGLE_CLOB, which reads a file as varchar(max)
- SINGLE_NCLOB, which reads a file as nvarchar(max)
- SINGLE_BLOB, which reads a file as varbinary(max)
*/
DECLARE @JSON VARCHAR(MAX)
SELECT @JSON = BulkColumn
FROM OPENROWSET
(BULK ‘E:\geeksforgeeks\json in sql\exchangerate.json’, SINGLE_CLOB)
AS j
— SELECT ISJSON(@JSON)
If (ISJSON(@JSON)=1)
SELECT baseCurrency,AsOnDate,INRRate,USDRate,GBPRate
INTO exchangeRatesTable
FROM OPENJSON (@JSON)
WITH (baseCurrency VARCHAR(10),AsOnDate VARCHAR(10),INRRate VARCHAR(20),
USDRate VARCHAR(20),
GBPRate VARCHAR(20)
)
SELECT * FROM exchangeRatesTable
Output:
You can refer the article in below link
Youtube : https://www.youtube.com/watch?v=lqJYMpIcPj8
Some of my technical articles can be seen at
GeeksForGeeks : https://auth.geeksforgeeks.org/user/priyarajtt/articles
