SQL Server 2025 introduces a convenient way to get data from a REST API endpoint directly through T-SQL and SQL Server utilities.
Prior ways of doing this usually involved using MSXML2.XMLHTTP (a COM object provided by Microsoft XML Core Services) through extended stored procedures, but with MSSQL 2025, there is a new SP, sp_invoke_external_rest_endpoint that is very readable and easy to use to get JSON (or XML) from an API response.
This brief article describes what an SP to get this data may look like, as well as the code to parse the JSON in the response to format the result as a table (vs. sending back all the JSON for the client to parse).
Here is an SP which fetches polling data for the Approval Polling data on current U.S. president Donald Trump:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: colin fitzgerald -- Create date: 20250625 -- Description: SP to fetch data from VoteHub API -- ============================================= CREATE OR ALTER PROCEDURE dbo.sp_GetVoteHubPollingData @LongView BIT = 0 AS BEGIN SET NOCOUNT ON; EXECUTE sp_configure 'external rest endpoint enabled', 1; RECONFIGURE WITH OVERRIDE; DECLARE @ret AS INT, @response AS NVARCHAR (MAX); EXECUTE @ret = sp_invoke_external_rest_endpoint @url = N'https://api.votehub.com/polls?poll_type=approval&subject=Trump', @headers = N'{"Accept":"application/json"}', @method = 'GET', @response = @response OUTPUT; ;WITH ResponseContent AS (SELECT [key], [value] FROM OPENJSON(@response)), ResponseJson AS (SELECT [value] FROM OPENJSON((SELECT [value] FROM ResponseContent WHERE [key]='result'))) SELECT --value, --id, pollster, [subject], poll_type, sample_size, created_at, approve, disapprove FROM ResponseJson OUTER APPLY OPENJSON(value) WITH( id nvarchar(255), pollster nvarchar(255), [subject] nvarchar(255), poll_type nvarchar(255), sample_size int, created_at datetime, approve decimal '$.answers[0].pct', disapprove decimal '$.answers[1].pct' ) WHERE created_at >= CASE WHEN @LongView = 0 THEN dateadd(mm, -3, getDate()) ELSE created_at END ORDER BY created_at DESC EXECUTE sp_configure 'external rest endpoint enabled', 0; RECONFIGURE WITH OVERRIDE; END GO
And here is the design view of the data source for a report using this polling table data:
If we CREATE dbo.sp_GetVoteHubPollingData as stored procedure on a database (in this case, I created it in 'master') that our data source connects to, then we can deploy the report to a Report Server or Power BI Report Server and run it:
References:
