What are the challenges you face when working across database platforms? Take the survey

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

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',


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


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



  • Options
    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. 
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
Sign In or Register to comment.