Insert XML or JSON format data from REST API call into SQL Server

Question

Hello and thank you in advance,

Is it possible to use REST API calls to retrieve data either in XML or JSON format from a script in SQL and then insert this data into records of a created table?

Let me be more clear with my question: The API is using REST "http://api.monitis.com/api/api.html"

Say for instance the output is as such:

<result>
<location id="4" name="UK1">
<row>
<cell>00:00</cell>
<cell>152</cell>
<cell>ok</cell>
</row>

So essentially I need to call through http REST API using the URL with parameters:

http://api.monitis.com/api?apikey=[apikey]&output=xml&version=2&action=testresult&testId=288571&day=03&month=10&year=2013&locationIds=1&timezone=-240

and then after retrieving the data I would need to be able to insert the data into a table after parsing for the data for 4 columns [location],[Time],[Response],[Status]

The process flow would be:

1.) Call the API with the desired parameters
2.) Serialize the XML
3.) Insert data into created table

Ok, could you point me in the right direction? Would I use httpwebrequest within sql then parse the data using a stored procedure? — gogo
SQL Server really doesn't have support or JSON parsing, nor for calling web services. You would be better off doing this from a front-end or middle-tier code (e.g. C# or something), getting the JSON result, parsing it, and then storing just the relevant bits into a SQL Server table — marc_s
So there is no way to call a web service through T-SQL to display the results and then parse the XML and insert the records into a table through a Stored Proc? — gogo

E.J. Brennan E.J. Brennan · Accepted Answer · 2013-10-08T17:41:43

I think its a really bad idea, but it should be possible.

Try this article for some tips to get you started:

http://www.databasejournal.com/features/mssql/article.php/3821271/Calling-a-Web-Service-from-within-SQL-Server.htm

but I agree with @marc_s, this would better be done in a different tier.

Insert XML or JSON format data from REST API call into SQL Server

1 Answers