Deep End with JSON and HTTP

  • So, I am a dabbler, and have been asked to help out at work.

    We have an API that delivers a JSON file. I need to be able to populate a table in SQL with the JSON data through a sp if possible

    The URL is http://servername/api/rest/22,23,24?date=2016-09-23

    and the output is

    [{"agentId":22,"firstName":"AGENT1","lastName":"AGNET","scheduleAdherence":98.0,"scheduleConformity":100.0},{"agentId":23,"firstName":"AGENT2","lastName":"AGNET2","scheduleAdherence":98.0,"scheduleConformity":99.0},{"agentId":24,"firstName":"AGENT3","lastName":"AGNET3","scheduleAdherence":97.0,"scheduleConformity":102.0}]

    I have tried the following but my response is always blank

    Declare @Object as Int;

    Declare @ResponseText as Varchar(8000);

    Declare @Body as varchar(8000);

    Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;

    EXEC sp_OAMethod @Object, 'open', NULL, 'post','http://servername/api/rest/22,23,24?date=2016-09-23', 'false'

    Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'

    Exec sp_OAMethod @Object, 'send', null, @body

    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

    Select @ResponseText [response]

    Exec sp_OADestroy @Object

  • You are setting the method to post but appears you should be doing a get. POST is should only used when you are passing a payload back to the API to do an insert. GET for request and PUT to update.


  • Thanks, that done it for me with another TEST url ,I must have something wrong with my API or JSON format

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply