fetch JSON data in sql server 2012

krunal pande

8y8.4k

Hi Team,

I want JSON data in table in sql server 2012.

Below json string in sql server, please provide solution ASAP.

{"success": true,"total":8,"status":[{"rowId":"b6beae86-e4b1-4d7d-9bde-e39a11b4ad35","employeeCode":"534006","centerName":"Alipore","centerCode":"KASE","date":"2016-08-29","acutalCheckinTime":"12:19","acutalCheckoutTime":null},{"rowId":"a1531325-4bcb-4144-8442-233b43abfeb3","employeeCode":"529372","centerName":"Alipore","centerCode":"KASE","date":"2016-08-29","acutalCheckinTime":"10:31","acutalCheckoutTime":null},{"rowId":"c0e478e2-9992-45cc-b086-852770801e3a","employeeCode":"529372","centerName":"Alipore","centerCode":"KASE","date":"2016-08-31","acutalCheckinTime":"10:00","acutalCheckoutTime":null},{"rowId":"44756cda-0750-4247-93f5-1f782b0215df","employeeCode":"534077","centerName":"Alipore","centerCode":"KASE","date":"2016-08-31","acutalCheckinTime":"10:00","acutalCheckoutTime":null},{"rowId":"d8f532bd-fac8-4ba9-a4e1-1aef8ce90cb2","employeeCode":"533427","centerName":"Alipore","centerCode":"KASE","date":"2016-08-29","acutalCheckinTime":"10:32","acutalCheckoutTime":null},{"rowId":"a176af7a-1413-47eb-babd-67242917aa26","employeeCode":"533427","centerName":"Alipore","centerCode":"KASE","date":"2016-08-31","acutalCheckinTime":"10:00","acutalCheckoutTime":null},{"rowId":"b2c4f683-e7ee-496a-9619-5f87f8784482","employeeCode":"533938","centerName":"Alipore","centerCode":"KASE","date":"2016-08-29","acutalCheckinTime":"11:21","acutalCheckoutTime":null},{"rowId":"3abd13f1-0269-49cc-8f2f-c3324a3edf96","employeeCode":"533938","centerName":"Alipore","centerCode":"KASE","date":"2016-08-31","acutalCheckinTime":"11:33","acutalCheckoutTime":null}]}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bryian Tan8y ago
Here is the link on how to do parse JSON on SQL 2012

http://mtkcode.blogspot.sg/2014/08/parse-json-string-by-sql-script.html

1. Download and execute the ParseJSON function from https://github.com/khanhmai/Parse-JSON-String-by-SQL-script/blob/master/ParseJSON-FUNCTION.sql
2. Run the following example to get idea how to display the JSON data into table.
DECLARE @json NVARCHAR(MAX)
SET @json =
N'{
   "success":true,
   "total":8,
   "status":[
      {
         "rowId":"b6beae86-e4b1-4d7d-9bde-e39a11b4ad35",
         "employeeCode":"534006",
         "centerName":"Alipore",
         "centerCode":"KASE",
         "date":"2016-08-29",
         "acutalCheckinTime":"12:19",
         "acutalCheckoutTime":null
      },
      {
         "rowId":"a1531325-4bcb-4144-8442-233b43abfeb3",
         "employeeCode":"529372",
         "centerName":"Alipore",
         "centerCode":"KASE",
         "date":"2016-08-29",
         "acutalCheckinTime":"10:31",
         "acutalCheckoutTime":null
      },
      {
         "rowId":"c0e478e2-9992-45cc-b086-852770801e3a",
         "employeeCode":"529372",
         "centerName":"Alipore",
         "centerCode":"KASE",
         "date":"2016-08-31",
         "acutalCheckinTime":"10:00",
         "acutalCheckoutTime":null
      },
      {
         "rowId":"44756cda-0750-4247-93f5-1f782b0215df",
         "employeeCode":"534077",
         "centerName":"Alipore",
         "centerCode":"KASE",
         "date":"2016-08-31",
         "acutalCheckinTime":"10:00",
         "acutalCheckoutTime":null
      },
      {
         "rowId":"d8f532bd-fac8-4ba9-a4e1-1aef8ce90cb2",
         "employeeCode":"533427",
         "centerName":"Alipore",
         "centerCode":"KASE",
         "date":"2016-08-29",
         "acutalCheckinTime":"10:32",
         "acutalCheckoutTime":null
      },
      {
         "rowId":"a176af7a-1413-47eb-babd-67242917aa26",
         "employeeCode":"533427",
         "centerName":"Alipore",
         "centerCode":"KASE",
         "date":"2016-08-31",
         "acutalCheckinTime":"10:00",
         "acutalCheckoutTime":null
      },
      {
         "rowId":"b2c4f683-e7ee-496a-9619-5f87f8784482",
         "employeeCode":"533938",
         "centerName":"Alipore",
         "centerCode":"KASE",
         "date":"2016-08-29",
         "acutalCheckinTime":"11:21",
         "acutalCheckoutTime":null
      },
      {
         "rowId":"3abd13f1-0269-49cc-8f2f-c3324a3edf96",
         "employeeCode":"533938",
         "centerName":"Alipore",
         "centerCode":"KASE",
         "date":"2016-08-31",
         "acutalCheckinTime":"11:33",
         "acutalCheckoutTime":null
      }
   ]
}'
--INSERT INTO WhatEverTable
Select
max(case when name='rowId' then convert(Varchar(50),StringValue) else '' end) as [rowId],
max(case when name='EmployeeCode' then convert(Varchar(50),StringValue) else '' end) as EmployeeCode,
  max(case when name='CenterName' then convert(Varchar(50),StringValue) else '' end) as CenterName,
max(case when name='CenterCode' then convert(Varchar(50),StringValue) else '' end) as CenterCode,
max(case when name='date' then convert(Varchar(50),StringValue) else '' end) as [date],
  max(case when name='AcutalCheckinTime' then convert(Varchar(50),StringValue) else '' end) as AcutalCheckinTime,
  max(case when name='AcutalCheckoutTime' then convert(Varchar(50),StringValue) else '' end) as AcutalCheckoutTime
From
    parseJSON
    (
           @json
    )
where ValueType = 'string'
group by parent_ID
krunal pande8y ago
Openjson function not supported in sql server 2012.
Bryian Tan8y ago
Use the OPENJSON function. Here is an example.

http://data.stackexchange.com/stackoverflow/query/747715/sql-json-to-table