How to parse json data in SQL server 2012?

• pinky_sam05

  SSC Veteran

  Points: 229

  More actions

  December 11, 2016 at 11:21 pm

  #332674

  I am using sql server 2012.I have been assigned a task where one of my column(JsonText) of table(Sample) contain json data.I want to parse that data and insert into columns of another table(Test).I searched on net 'openjson'is supported in sql server 2016.How to do in sql server2012.

  Table1 : Sample

  Id JsonText Active

  JsonText

  webaddress?{'data':'{"PId": "XXXX","Status": "YES","Name":"XXX","Address":"XXXX","MobileNumber":"xxx"}'}

  I am intrested only 'PID,Address,MobileNumber' columns not all.

  Table Test structure like this

  Id,PID,Address,MobileNumber

• DesNorton

  SSC-Insane

  Points: 24501

  More actions

  December 12, 2016 at 1:23 am

  #1917308

  pinky_sam05 (12/11/2016)

  ---

  I am using sql server 2012.I have been assigned a task where one of my column(JsonText) of table(Sample) contain json data.I want to parse that data and insert into columns of another table(Test).I searched on net 'openjson'is supported in sql server 2016.How to do in sql server2012.

  Table1 : Sample

  Id JsonText Active

  JsonText

  webaddress?{'data':'{"PId": "XXXX","Status": "YES","Name":"XXX","Address":"XXXX","MobileNumber":"xxx"}'}

  I am intrested only 'PID,Address,MobileNumber' columns not all.

  Table Test structure like this

  Id,PID,Address,MobileNumber

  SELECT

  PId = SUBSTRING(src.JsonText, P.sPos, P.ePos - P.sPos)

  , [Address] = SUBSTRING(src.JsonText, A.sPos, A.ePos - A.sPos)

  , MobileNumber = SUBSTRING(src.JsonText, M.sPos, M.ePos - M.sPos)

  FROM #Sample AS src

  CROSS APPLY (SELECT PATINDEX('%"PId"%', src.JsonText)+8, CHARINDEX('"', src.JsonText, PATINDEX('%"PId"%', src.JsonText)+8)) P(sPos, ePos)

  CROSS APPLY (SELECT PATINDEX('%"Address"%', src.JsonText)+11, CHARINDEX('"', src.JsonText, PATINDEX('%"Address"%', src.JsonText)+11)) A(sPos, ePos)

  CROSS APPLY (SELECT PATINDEX('%"MobileNumber"%', src.JsonText)+16, CHARINDEX('"', src.JsonText, PATINDEX('%"MobileNumber"%', src.JsonText)+16)) M(sPos, ePos)

  ---

  How to post data/code on a forum to get the best help.

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  December 12, 2016 at 1:54 am

  #1917311

  Phil Factor wrote a long piece on this few years back Consuming JSON Strings in SQL Server[/url]