Calling API from SQL Server and storing the JSON result into the table

Sometimes we need to call the API and extract the data from the API and store it into the table. There is multiple ways to call the API to extract the data.

·         Using SQL script

·         Using SSIS package

·         Call API using the application

In this post, we will see how to call the API from SQL Server using SQL script and loading that data into the table.

First of all, we need to enable the OLE automation procedures using the below script.

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO

             

Below are the steps

·         Create the URL

·         Pass the parameters if required

·         Create an OLE object using the sp_OACreate procedure

·         Pass the created OLE object and make an HTTP request call

·         Handle the response received from API

·         Parse the JSON records and insert/ update in the desired table

Creating the URL

DECLARE @url as nvarchar(max) = 'https://jsonplaceholder.typicode.com/posts'

For testing I am using the  below site for free API testing

https://jsonplaceholder.typicode.com/

Here we are using the posts endpoint.   

Suppose we are using the get method and passing the parameter to the API in this case we need to pass that parameter in the url.

DECLARE  @url aDECLARE @userid int = 1; DECLARE  @url as nvarchar(max) = 'https://jsonplaceholder.typicode.com/posts?userId=' set @url = CONCAT(@url, @userid);

Create an OLE object using the sp_OACreate procedure

sp_OACreate  is use to create an instance of an OLE object. The newly created OLE object is automatically destroyed at the end of the Transact-SQL statement batch.

After that we are calling the sp_OAMethod and sp_OAGetProperty to call the API and invoking and receiving the API response.

After that parsing the JSON file data and inserting or updating or delete the records in the table.

Below is the JSON data  

Below is the table

create table Posts_details ( userID int, Id int, Post_title varchar(100), Post_body  varchar(500) )

Below is the SQL Script

DECLARE @status int DECLARE @responseText as table(responseText nvarchar(max)) DECLARE @res as Int; DECLARE @url as nvarchar(max) = 'https://jsonplaceholder.typicode.com/posts' EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @res OUT EXEC sp_OAMethod @res, 'open', NULL, 'GET',@url,'false' EXEC sp_OAMethod @res, 'send' EXEC sp_OAGetProperty @res, 'status', @status OUT INSERT INTO @ResponseText (ResponseText) EXEC sp_OAGetProperty @res, 'responseText'   IF exists(SELECT  responseText FROM @responseText) BEGIN      DECLARE @json NVARCHAR(MAX) = (SELECT  responseText FROM @responseText)                                    insert into Posts_details                  (                                 userID ,                                 Id ,                                 Post_title ,                                 Post_body                   )      SELECT userId,                                                 id,                                                 title,                                                 body      FROM OPENJSON(@json)           WITH (                  userId NVARCHAR(30) '$.userId',                  id NVARCHAR(50) '$.id',                  title NVARCHAR(50) '$.title',                  body NVARCHAR(50) '$.body'                ); END ELSE BEGIN      DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';      Print @ErroMsg; END EXEC sp_OADestroy @res

 Executing this script.        

Executed successfully.

See the records in the table.

      

Suppose we are using the get method and passing the parameter to the API in this case we need to pass that parameter in the url.

DECLARE  @url aDECLARE @userid int = 1; DECLARE  @url as nvarchar(max) = 'https://jsonplaceholder.typicode.com/posts?userId=' set @url = CONCAT(@url, @userid);

All code are same

DECLARE @userid int = 1; DECLARE @status int DECLARE @responseText as table(responseText nvarchar(max)) DECLARE @res as Int; DECLARE  @url as nvarchar(max) = 'https://jsonplaceholder.typicode.com/posts?userId=' set @url = CONCAT(@url, @userid); EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @res OUT EXEC sp_OAMethod @res, 'open', NULL, 'GET',@url,'false' EXEC sp_OAMethod @res, 'send' EXEC sp_OAGetProperty @res, 'status', @status OUT INSERT INTO @ResponseText (ResponseText) EXEC sp_OAGetProperty @res, 'responseText'   IF exists(SELECT  responseText FROM @responseText) BEGIN      DECLARE @json NVARCHAR(MAX) = (SELECT  responseText FROM @responseText)                                    insert into Posts_details                  (                                 userID ,                                 Id ,                                 Post_title ,                                 Post_body                   )      SELECT userId,                                                 id,                                                 title,                                                 body      FROM OPENJSON(@json)           WITH (                  userId NVARCHAR(30) '$.userId',                  id NVARCHAR(50) '$.id',                  title NVARCHAR(50) '$.title',                  body NVARCHAR(50) '$.body'                ); END ELSE BEGIN      DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';      Print @ErroMsg; END EXEC sp_OADestroy @res

Before running this script record in the table.    

Running the script.

        

See the records in the tables.  

Hope this will help us to read the data from the API and store it in the database table.

In the next post, we will see how we call API using The SSIS and storing the data into the database.