how parse Big JSON result in SQL table in sql server 2014

lakshmiSundari

HI Team,

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 @Objectas Int;

Declare @ResponseTextas Varchar(MAX);

--Code Snippet

Exec sp_OACreate 'MSXML2.XMLHTTP', @ObjectOUT;

Exec sp_OAMethod @Object, 'open', NULL, 'get',

                APIurl,'false'

Exec sp_OAMethod @Object,  'setRequestHeader', null, 'Content-Type', 'application/xml'

Exec sp_OAMethod @Object, 'send'

Exec sp_OAMethod @Object, 'responseText', @ResponseTextOUTPUT

select @ResponseText

Select * from dbo.json_Parse(@ResponseText)

Exec sp_OADestroy @Object

Output :  @ResponseText = NULL

and

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=4.0.0.0,’ 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

Dan B

Hi lakshmiSundari,

Thanks for posting on the Redgate forums. 

I believe this request would be better aimed at the wider SQL community and is more likely to get a response from a website such as http://www.sqlservercentral.com/. Where the user base can assist with SQL Server specifics.