Make HTTP calls from SQL server
Example – 1
Basic code with http call. Get JSON response and parse JSON
DECLARE @status int DECLARE @responseText as table(responseText nvarchar(max)) DECLARE @res as Int; DECLARE @url as nvarchar(1000) = 'http://webserver.com/path/url_page' 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' EXEC sp_OADestroy @res --SELECT responseText FROM @responseText --SELECT @status, responseText FROM @responseText Select max(case when name='Key1' then convert(Varchar(50),StringValue) else '' end) as [Key1], .... max(case when name='Key2' then convert(Varchar(50),StringValue) else '' end) as [Key2] From parseJSON ( (SELECT responseText FROM @responseText) ) where ValueType = 'string' OR ValueType = 'boolean' group by parent_ID
Example – 2
Return code checks before proceeding to next steps
-- source: https://stackoverflow.com/questions/22067593/calling-an-api-from-sql-server-stored-procedure/52686957 Declare @Object as Int; DECLARE @hr int Declare @json as table(Json_Table nvarchar(max)) Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT; IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get', 'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked) 'false' IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object Exec @hr=sp_OAMethod @Object, 'send' IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText' -- select the JSON string select * from @json -- Parse the JSON string SELECT * FROM OPENJSON((select * from @json), N'$.elements') WITH ( [type] nvarchar(max) N'$.type' , [id] nvarchar(max) N'$.id', [lat] nvarchar(max) N'$.lat', [lon] nvarchar(max) N'$.lon', [amenity] nvarchar(max) N'$.tags.amenity', [name] nvarchar(max) N'$.tags.name' ) EXEC sp_OADestroy @Object
Example – 3
Bare bone code
Declare @Object as Int; Declare @ResponseText as Varchar(8000); Code Snippet Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; Exec sp_OAMethod @Object, 'open', NULL, 'get', 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked) 'false' Exec sp_OAMethod @Object, 'send' Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT Select @ResponseText Exec sp_OADestroy @Object
HTH