Sample Header Ad - 728x90

Passing a variable in OPENJSON WITH statement in SQL Server

0 votes
1 answer
2709 views
Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from api we have to specify '$.rates.GBP' in openjason statement to read value of FX(last statement of below script). I am not able to pass this as a parameter to make SampleValue dynamic. I tried using dynamic SQL but did not succeed. Please help.
/*
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'Ole Automation Procedures';
GO


*/
DECLARE   @Object	Int
		, @hr		int
		, @Base		char(3)
		, @Symbol   char(3)
		, @Url		varchar(1000)
		,@Param varchar(1000)
DECLARE   @json  as table(Json_Table nvarchar(max))

SET @Base   = 'USD'
SET @Symbol ='GBP'

SET @Url = 'https://api.ratesapi.io/api/latest?base= ' + @Base + '&symbols=' + @Symbol    ;--Your Web Service Url (invoked)
SET @Param = '$.rates.' + @Symbol;

select @Url;  --https://api.ratesapi.io/api/latest?base=USD&symbols=GBP 

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr  0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL,'get', @Url, 'false'
IF @hr  0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr  0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr  0 EXEC sp_OAGetErrorInfo @Object

INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'

SELECT  *
FROM OPENJSON((select Json_table from @json), N'$')
WITH (   
      SampleValue nvarchar(max) '$.rates.GBP' ,   
	  SampleDate DATE '$.date'
	  ) ;

	/*DECLARE @SQL NVARCHAR(MAX) = 
	'SELECT  *  FROM OPENJSON((select Json_table from @json), N''$'') WITH (           SampleValue  nvarchar(max)  ''$.rates.GBP'',  SampleDate DATE ''$.date'' ) ;'

 select @SQL; 
 EXEC sp_executesql @SQL;

	  DECLARE @SQLstring NVARCHAR(MAX) = 
'SELECT  *
FROM OPENJSON((select Json_table from @json), N''$'') WITH (   
      SampleValue nvarchar(max) '+ ''''+ @param + ''''+ ' ,   
	  SampleDate DATE'  + ''''+ '$.date'  + '''' + ' ) ;'

	  select @SQLstring ; 
EXEC sp_executesql @SQLstring ;*/

EXEC sp_OADestroy @Object
Asked by Ruhi Afreen (1 rep)
Oct 15, 2020, 10:56 AM
Last activity: Oct 15, 2020, 06:22 PM