kpitsimpl: PBI

SQL Server 2025 introduces a convenient way to get data from a REST API endpoint directly through T-SQL and SQL Server utilities.

Outlined in lime green are the two items SQL Server 2025 handles well, discussed in this post

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:

And here is the design view of the report that will use this 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:

This is the report as rendered within Power BI Report Server's Portal using extRSAuth for authentication and authorization

This is the report as rendered within extRSAuth custom PBIRS reporting portal on extrs.net

Lots of neat stuff you can do with the new features of SQL Server 2025- check 'em out.

Next up: embedding a PBI version of this report in extrs.net, and embedding a HighCharts JS version of this report in extrs.net- all using this dbo.sp_GetVoteHubPollingData SP that uses sp_invoke_external_rest_endpoint.

And I'll also include a short YouTube stepping through some of the mechanics of extRS and how it calls the SSRS API to facilitate a custom SSRS portal implementation that you or your organization may be interested in implementing.

References:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql

https://learn.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server

SQL Server 2025's sp_invoke_external_rest_endpoint with OPENJSON CTE for quickly and easily getting data from REST APIs