Import/Read a JSON string and convert it in tabular (row/column) form | SQL Server 2016 – P...


In my [previous post] I discussed on how to format/convert any Query/Table data to a string in JSON format. JSON in SQL Server was limited to this feature only till the CTP 2 version.

But now with the CTP 3 release you can do reverse of it also, means now you can read back JSON data and convert it to tabular or row & column format.

Let’s check this by taking same sample data from our previous JSON-Export post .

DECLARE @json NVARCHAR(1000)SELECT @json = N'{"StudList":[ { "ID":1,"FirstName":"Manoj","LastName":"Pandey","Class":10,"Marks":80.5},{ "ID":2,"FirstName":"Saurabh","LastName":"Sharma","Class":11,"Marks":82.7},{ "ID":3,"FirstName":"Kanchan","LastName":"Pandey","Class":10,"Marks":90.5},{ "ID":4,"FirstName":"Rajesh","LastName":"Shah","Class":11,"Marks":70.3},{ "ID":5,"FirstName":"Kunal","LastName":"Joshi","Class":12,"Marks":64.7}]'SELECT ID, FirstName, LastName, Class, MarksFROM OPENJSON (@json, '$.StudList')WITH (ID INT, FirstName VARCHAR(255), LastName VARCHAR(255), Class INT,Marks DECIMAL(3,1)) AS StudList

– Output:

Well, that was simple, isn’t it!!!

I’ll be talking more about JSON support in SQL Server in my coming posts, so stay tuned!!!