-- ✅ Enable external REST endpoint feature (run once per server)
EXEC sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;
----------------------------------------------------------
-- ✅ 1. GET Request - Retrieve a specific post
----------------------------------------------------------
DECLARE @getResponse NVARCHAR(MAX);
EXEC sp_invoke_external_rest_endpoint
@url = 'https://jsonplaceholder.typicode.com/posts/1',
@response = @getResponse OUTPUT;
SELECT @getResponse AS GET_Response;
----------------------------------------------------------
-- ✅ 2. POST Request - Create a new post
----------------------------------------------------------
DECLARE @postResponse NVARCHAR(MAX);
EXEC sp_invoke_external_rest_endpoint
@url = 'https://jsonplaceholder.typicode.com/posts',
"body": "Content from SQL Server"
@response = @postResponse OUTPUT;
SELECT @postResponse AS POST_Response;
----------------------------------------------------------
-- ✅ 3. PUT Request - Replace entire post (full update)
----------------------------------------------------------
DECLARE @putResponse NVARCHAR(MAX);
EXEC sp_invoke_external_rest_endpoint
@url = 'https://jsonplaceholder.typicode.com/posts/1',
"title": "Updated Title",
"body": "Updated content"
@response = @putResponse OUTPUT;
SELECT @putResponse AS PUT_Response;
----------------------------------------------------------
-- ✅ 4. PATCH Request - Partially update a post
----------------------------------------------------------
DECLARE @patchResponse NVARCHAR(MAX);
EXEC sp_invoke_external_rest_endpoint
@url = 'https://jsonplaceholder.typicode.com/posts/1',
"title": "Partially Updated Title"
@response = @patchResponse OUTPUT;
SELECT @patchResponse AS PATCH_Response;
----------------------------------------------------------
-- ✅ 5. DELETE Request - Delete a post
----------------------------------------------------------
DECLARE @deleteResponse NVARCHAR(MAX);
EXEC sp_invoke_external_rest_endpoint
@url = 'https://jsonplaceholder.typicode.com/posts/1',
@response = @deleteResponse OUTPUT;
SELECT @deleteResponse AS DELETE_Response;
-- Step 1: Declare variables
DECLARE @response NVARCHAR(MAX);
DECLARE @rawArray NVARCHAR(MAX);
-- Step 2: Send GET request to fetch all posts
EXEC sp_invoke_external_rest_endpoint
@url = 'https://jsonplaceholder.typicode.com/posts',
@response = @response OUTPUT;
-- Optional: View full wrapped JSON
SELECT @response AS RawJson;
-- Step 3: Extract array string from result (use JSON_VALUE if it's double-encoded)
SET @rawArray = JSON_QUERY(@response, '$.response.result');
-- Extract the array correctly using JSON_QUERY (not JSON_VALUE!)
DECLARE @fixedJson NVARCHAR(MAX);
SET @fixedJson = JSON_QUERY(@response, '$.result');
-- Now parse the array using OPENJSON
JSON_VALUE([value], '$.userId') AS UserId,
JSON_VALUE([value], '$.id') AS Id,
JSON_VALUE([value], '$.title') AS Title,
JSON_VALUE([value], '$.body') AS Body
FROM OPENJSON(@fixedJson);
-- Extract first object from array manually
DECLARE @first NVARCHAR(MAX);
SET @first = JSON_QUERY(@response, '$.result[0]');
JSON_VALUE(@first, '$.userId') AS UserId,
JSON_VALUE(@first, '$.id') AS Id,
JSON_VALUE(@first, '$.title') AS Title,
JSON_VALUE(@first, '$.body') AS Body;