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
Last activity: Oct 15, 2020, 06:22 PM