Can we do Parse JSON file in SQL server 2014 version?

Question

Can we do Parse JSON file in SQL server 2014 version?

manavairajan

Right there with Babe

Points: 781
More actions
April 16, 2018 at 10:23 am

#410424

Hello,
I need a help from you. Could you please confirm is it possible to parse JSON file in SQL server 2014 version?
Thanks
Rajan. S

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

manavairajan - Monday, April 16, 2018 10:23 AM
Hello,
I need a help from you. Could you please confirm is it possible to parse JSON file in SQL server 2014 version?
Thanks
Rajan. S

Almost all things are possible in SQL Server, given enough time and effort, but native support is available only from 2016 onwards.
SSIS can parse JSON files, with a bit of scripting. Or you could write CLRs to help you do it in T-SQL.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You could take a look at this for options on using JSON before SQL Server 2016.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/consuming-json-strings-in-sql-server/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Luis Cazares - Monday, April 16, 2018 11:06 AM
You could take a look at this for options on using JSON before SQL Server 2016.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/consuming-json-strings-in-sql-server/

We are using a JSON parser written by Phil Factor on a SQL Server 2012 server. It works, but I did have to do a slight work-around to be successful with it. I had to remove all spaces on either side of the ":" to get it to parse the JSON strings we were provided.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Chipping in (same as Lynn suggested) have a look here
😎

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Lynn Pettis - Monday, April 16, 2018 12:35 PM
Luis Cazares - Monday, April 16, 2018 11:06 AM
You could take a look at this for options on using JSON before SQL Server 2016.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/consuming-json-strings-in-sql-server/
We are using a JSON parser written by Phil Factor on a SQL Server 2012 server. It works, but I did have to do a slight work-around to be successful with it. I had to remove all spaces on either side of the ":" to get it to parse the JSON strings we were provided.

That's a good thing to know. Thank you for the note.
I haven't had to work with JSON and I hope to never have to do it (in SQL).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Luis Cazares - Friday, April 20, 2018 9:49 AM
Lynn Pettis - Monday, April 16, 2018 12:35 PM
Luis Cazares - Monday, April 16, 2018 11:06 AM
You could take a look at this for options on using JSON before SQL Server 2016.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/consuming-json-strings-in-sql-server/
We are using a JSON parser written by Phil Factor on a SQL Server 2012 server. It works, but I did have to do a slight work-around to be successful with it. I had to remove all spaces on either side of the ":" to get it to parse the JSON strings we were provided.
That's a good thing to know. Thank you for the note.
I haven't had to work with JSON and I hope to never have to do it (in SQL).

Working with JSON in 2016 and newer is so much easier.