How Read JSON Data From URL In SQL Server

Question

I'm trying to read JSON data from a URL in SQL Server 2008, using this code:

DECLARE @temp table (RowNum int, DATA NVARCHAR(max))
DECLARE @url VARCHAR(MAX),
@win INT,
@hr INT,
@Text VARCHAR(8000),
@RowID int,
@Status smallint,
@Accuracy tinyint

Set @url = 'http://www.jsonprovider.com/api/json/2020'
EXEC @hr = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Open', NULL, 'GET', @url, 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAGetProperty @win, 'ResponseText', @Text OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OADestroy @win 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

select  @Text  --<< Result

AND I get my result.

The problem is the process to get this result takes 7 sec in SQL Server, but in normal call by C# application and calling in web browser I get my result in 0.06 sec.

I looking for a solution for improving my code to get a fast result.

You are trying to read JSON data from a webpage directly into sql? Why? Why not read it in c# and pass it in. From your numbers that would be much faster. — gunr2171
– gunr2171, Commented Oct 10, 2013 at 23:09
I was able to use this code to read json text from a url by changing the ole object name to MSXML2.ServerXMLHTTP with fast results. — David
– David, Commented Jul 12, 2022 at 19:16

E.J. Brennan · Accepted Answer · 2013-10-10 23:31:15Z

7

This is an example of "just because you can, doesn't mean you should".

SQL Server is not the right tool for this job; the fact that it works at all is amazing. If you want to speed it up, put in in a c# or php program and let SQL server do that job it was intended for. Processing jSON responses is not one of them.

answered Oct 10, 2013 at 23:31

E.J. Brennan

46.9k8 gold badges94 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

akaco Over a year ago

oK I did it Here ???

Alesis Joan Over a year ago

This is an axample of "this is not an answer".

Community · Accepted Answer · 2017-05-23 11:50:05Z

2

E.J. explained it quite well, I agree with him.

I would like to add that if you want to keep everything in database code you can try CLR procedures that are essentially written in C# but work inside SQL Server.

See these links for details on how to do this in C#

HTTP request with post
How to make a GET request by using Visual C#

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Oct 11, 2013 at 13:47

Logan Merrit

1313 bronze badges

Collectives™ on Stack Overflow

How Read JSON Data From URL In SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related