Hi @Spunny ,
Maybe you could try using SSIS to call a web service and then store and query the results.
https://learn.microsoft.com/en-us/sql/integration-services/control-flow/web-service-task?view=sql-server-ver16
You can use web service tasks to read data from and send data to a web service.
https://www.sqlshack.com/ssis-web-service-tasks
Calling the ssis package from a stored procedure without using xp_cmdshell is discussed in the SSIS documentation.
https://learn.microsoft.com/en-us/sql/integration-services/run-manage-packages-programmatically/loading-and-running-a-remote-package-programmatically?redirectedfrom=MSDN&view=sql-server-ver16
Another option would be a CLR procedure that runs the package, so instead of TSQL/xp_cmdshell you use CLR/Dts object model.
https://learn.microsoft.com/en-us/sql/integration-services/run-manage-packages-programmatically/loading-and-running-a-local-package-programmatically?redirectedfrom=MSDN&view=sql-server-ver16
Best regards,
Lan Huang
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.