httpwebclient request and insert to sql server code example

Example: 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;