httpwebclient request and insert to sql server
-- Query the Stack Overflow API and get a response
DECLARE @response XML =
[dbo].[clr_http_request]
(
'GET', 'http://api.stackexchange.com/2.2/questions?site=stackoverflow',
NULL, NULL, 300000, 1, 0
);
-- Extract just the body of the response (expecting JSON)
DECLARE @response_json NVARCHAR(MAX) = @response.value('Response[1]/Body[1]', 'NVARCHAR(MAX)');
-- Parse the JSON into a tabular format
SELECT
B.[question_id],
B.[title],
B.[tags],
B.[is_answered],
B.[view_count],
B.[answer_count],
B.[score]
FROM OPENJSON(@response_json) WITH ([items] NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.[items]) WITH
(
[question_id] INT,
[title] NVARCHAR(MAX),
[tags] NVARCHAR(MAX) AS JSON,
[is_answered] BIT,
[view_count] INT,
[answer_count] INT,
[score] INT
) B;