Showing posts with label REST. Show all posts
Showing posts with label REST. Show all posts

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

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.


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

extRS for useful common logic, reference data and extending SSRS

extRS is a .NET assembly, available as a Nuget package here, which is a utility for getting data out of SSRS, FedEx, UPS, USPS, financial markets, among several other useful functionalities.       
  
            

extRS was initially designed as a set of helper functions to access things in RS, but it evolved to contain a lot more..



Here are a couple examples of some of the useful things you can do with extRS:

Create, Get and Delete an SSRS Report Subscription
     [TestMethod]  
     public async Task CreateGetDeleteSubscriptionSucceeds()  
     {  
       string json = @"{  
       ""@odata.context"": ""https://localhost/reports/api/v2.0/$metadata#Subscriptions/$entity"",  
         ""Owner"": """ + Resources.User + @""",  
         ""IsDataDriven"": false,  
         ""Description"": ""string..."",  
         ""Report"": ""/Reports/USPolls"",  
         ""IsActive"": true,  
         ""EventType"": ""TimedSubscription"",  
         ""ScheduleDescription"": ""string..."",  
         ""LastRunTime"": ""2023-04-13T15:51:04Z"",  
         ""LastStatus"": ""string..."",  
         ""DeliveryExtension"": ""Report Server Email"",  
         ""LocalizedDeliveryExtensionName"": ""Email"",  
         ""ModifiedBy"": """ + Resources.User + @""",  
         ""ModifiedDate"": ""2023-04-13T15:51:04Z"",  
         ""Schedule"": {  
           ""ScheduleID"": null,  
           ""Definition"": {  
             ""StartDateTime"": ""2021-01-01T02:00:00-07:00"",  
             ""EndDate"": ""0001-01-01T00:00:00Z"",  
             ""EndDateSpecified"": false,  
             ""Recurrence"": {  
               ""MinuteRecurrence"": null,  
               ""DailyRecurrence"": null,  
               ""WeeklyRecurrence"": null,  
               ""MonthlyRecurrence"": null,  
               ""MonthlyDOWRecurrence"": null  
             }  
           }  
         },  
         ""DataQuery"": null,  
         ""ExtensionSettings"": {  
           ""Extension"": ""DeliveryExtension"",  
           ""ParameterValues"": [  
             {  
               ""Name"": ""TO"",  
               ""Value"": ""colin@sonrai.io"",  
               ""IsValueFieldReference"": false  
             },  
             {  
               ""Name"": ""IncludeReport"",  
               ""Value"": ""true"",  
               ""IsValueFieldReference"": false  
             },  
             {  
               ""Name"": ""Subject"",  
               ""Value"": ""true"",  
               ""IsValueFieldReference"": false  
             },  
             {  
               ""Name"": ""RenderFormat"",  
               ""Value"": ""PDF"",  
               ""IsValueFieldReference"": false  
             }  
           ]  
         },  
         ""ParameterValues"": []  
       }";  
       Subscription subscription = await ssrs.SaveSubscription(JsonConvert.DeserializeObject<Subscription>(json)!);  
       Assert.IsTrue(subscription.DeliveryExtension != null);  
       var getResponse = await ssrs.GetSubscription(subscription.Id.ToString()!);  
       Assert.IsTrue(getResponse.Id != null);  
       var delResp = await ssrs.DeleteSubscription(subscription.Id.ToString()!);  
       Assert.IsTrue(delResp);  
     }  


Getting FedEx/USPS/UPS Shipping Rates






"A meaningful and useful abstraction that is able to withstand the test of time is very difficult to design. The main difficulty is getting the right set of members—no more and no fewer. If an abstraction has too many members, it becomes difficult or even impossible to implement. If it has too few members for the promised functionality, it becomes useless in many interesting scenarios." -Designing for Extensibility in .NET (Cwalina, Abrams, Barton)