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

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  April 16, 2018 at 10:35 am

  #1987023

  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.

  ---

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 16, 2018 at 11:06 am

  #1987027

  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

  April 16, 2018 at 12:35 pm

  #1987041

  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: 182947

  More actions

  April 16, 2018 at 12:39 pm

  #1987043

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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 20, 2018 at 9:49 am

  #1987693

  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

  April 20, 2018 at 9:59 am

  #1987694

  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.