Send JSON Data to Webservices SQL

Question

Good Day Developers

Interesting thought

is it actually possible to send JSON data outputs to a WebService via SQL Stored Procedure I'm trying to understand how this logic works, i have been researching online and nothing tangible is showing

Answer 1

It is possible with plain T-SQL, but only with painful Xtended OLE Automation programming; believe me, you don't want that; see OLE Automation Objects in Transact-SQL and https://www.codeproject.com/Articles/428200/How-to-invoke-a-Web-Service-from-a-Stored-Procedur

Second option is a .NET CLR, but that requieres .NET programming knowledge, https://www.c-sharpcorner.com/blogs/calling-web-service-from-sql-server-using-sql-clr

Better option is doing it from outside SQL Server, e.g. using PowerShell or SSIS package with a C# script task; also this requires .NET programming knowledge.

Answer 2

Hi @Ronald Van Der Westhuizen ,

Thank you so much for posting here.

You could firstly import the JSON data into SQL Server table like below:

create table Customer  
(  
CustomerNumber int,  
CustomerTag int,  
PurchaseTypeIndicator decimal(18,1),  
Result varchar(20)  
)  

DECLARE @json nvarchar(max)=N'{"Customer":[{"CustomerNumber":122224,"CustomerTag":6111112,"PurchaseTypeIndicator":38.0,"Result":"High"}]}';  
  
insert into Customer   
SELECT JSON_VALUE(@json, '$.Customer[0].CustomerNumber') CustomerNumber  
,JSON_VALUE(@json, '$.Customer[0].CustomerTag') CustomerTag  
,JSON_VALUE(@json, '$.Customer[0].PurchaseTypeIndicator') PurchaseTypeIndicator  
,JSON_VALUE(@json, '$.Customer[0].Result') Result  
  
select * from Customer  

Output:

CustomerNumber CustomerTag PurchaseTypeIndicator Result  
122224 6111112 38.0 High  

Then you could refer below and check whether any of them is helpful.
Generate JSON Data Using Web Service And SQL Server Stored Procedure
Can you call a webservice from TSQL code?

In addition, you could also try with a high level to send data to a web service API by using SSIS.

Reference:
How to pass values to a Web Service from SSIS Script task?
Consume Webservice via SSIS Script Component

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

See https://www.c-sharpcorner.com/article/how-to-consume-web-api-through-ssis-package/ for a SSIS solution