how parse Big JSON result in SQL table in sql server 2014
I've called API in SQL Sever and it returning JSON. I was trying to store the result in variable VARCHAR(MAX). But my data is huge (around hundreads records with 10 to 20 columnns if we convert it as table in SQL) so the length is not supporting, so no data is storing in the variable, it is becoming NULL.
I'm using SQL server 2014.
Declare @Object as Int;
Declare @ResponseText as Varchar(MAX);
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/xml'
Exec sp_OAMethod @Object, 'send'
Select * from dbo.json_Parse(@ResponseText)
Exec sp_OADestroy @Object
Output : @ResponseText = NULL
I tried API consuming logic in .net and tried to create an assembly from SQL.(create ASSEMBLY jsontest from 'c:\dll\JsonParser.dll' WITH PERMISSION_SET =EXTERNAL_ACCESS)
But it is giving me the error as ‘Assembly 'JsonParser' references assembly 'system.net.http, version=126.96.36.199,’ as it was dependent on other built-in assembly System.Net.Http
so again tried to create the references assembly 'system.net.http’ But it is giving me the error as
‘CREATE ASSEMBLY failed because type 'System.Net.Http.HttpContent' in safe assembly 'System.Net.Http' has a static field 'EncodingsWithBom'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language’
Please help in this I did a lot of R&D but nothing worked for me. My version also in 2014, there is no planning to upgrade currently. I hope i'll get solution from experts here.
Thanks in advance