Advertisements
Home > JSON, SQL Server 2016 > Import/Read a JSON string and convert it in tabular (row/column) form | SQL Server 2016 – Part 2

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


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, Marks
FROM OPENJSON (@json, '$.StudList')
WITH (
	ID INT, 
	FirstName VARCHAR(255), 
	LastName VARCHAR(255), 
	Class INT,
	Marks DECIMAL(3,1)
) AS StudList

– Output:
SQL Server 2016 - JSON export 01

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


 

In my [next post] I’ll talk about Nested Properties in a JSON string with single and multiple elements.
 


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: