Welcome to the SQL-APIConsumer wiki!

CREATE DATABASE TestDB; GO

USE TestDB GO sp_configure 'clr enabled',1 RECONFIGURE GO

ALTER DATABASE TESTDB SET TRUSTWORTHY ON GO

CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE--external_access GO

CREATE ASSEMBLY [Newtonsoft.Json] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE go

CREATE ASSEMBLY [API_Consumer] AUTHORIZATION dbo FROM N'C:\CLR\API_Consumer.dll' WITH PERMISSION_SET = UNSAFE

GO

PRINT N'Creating [dbo].[APICaller_GET_Json]...'; GO CREATE PROCEDURE [dbo].[APICaller_GET_Json] @URL NVARCHAR (MAX) NULL AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_GET_Json]

-- How to consume GET API -- How to show Json results.

DECLARE @RoutingNumber AS VARCHAR(50) = '122242597'

--Public API: routingnumbers.info DECLARE @Url VARCHAR(200) = CONCAT('https://www.routingnumbers.info/api/name.json?','rn=',@RoutingNumber)

DECLARE @Results AS TABLE ( Context varchar(max) )

DECLARE @Result AS VARCHAR(MAX)

INSERT INTO @Results EXEC [dbo].[APICaller_GET_Json] @Url

--Result: Row per value

SELECT B.* FROM ( SELECT Context from @Results )tb OUTER APPLY OPENJSON (context) B

--Result: column per value. SELECT [name] ,[rn] ,[message] ,[code] FROM ( SELECT Context from @Results )tb OUTER APPLY OPENJSON (context) WITH ( [name] VARCHAR(20) '$.name' , [rn] VARCHAR(20) '$.rn' , [message] VARCHAR(20) '$.message' , [code] INT '$.code' );

 EXEC [dbo].APICaller_POST @URL = ' http://localhost:5000/api/auth/login' , @Body = '{"username": "geraldo","password": "password"}' EXEC [dbo].APICaller_GETAuth @URL = 'http://localhost:5000/api/values' , @Token = 'Bearer aeyJhbGciOiJIUzUxMiIsInR5cCI6IkpXVCJ9.eyJuYW1laWQiOiIxIiwidW5pcXVlX25hbWUiOiJnZXJhbGRvIiwibmJmIjoxNTM4MTA3NDkxLCJleHAiOjE1MzgxOTM4OTEsImlhdCI6MTUzODEwNzQ5MX0.j9kX5KXJP6yHBJZZK07tNQayyUkuQf8CtoDDDwdPISZy0eb9RQvnooB3oMND54-5Yzv5LMO9nuM69t2PJh5iXw'