Get JSON using SQL Server 2016 or Azure SQL Database

Here I will explain how to get JSON from SQL Server 2016 and Azure SQL Database. In SQL Server 2016 we will get JSON easily using "FOR json AUTO;" after the select statment.

Here I will create one temp table for save data.

CREATE TABLE #tmp(
[FCAT_1] [varchar](500) NULL,
[FCAT_2] [varchar](500) NULL

)

INSERT INTO #tmp( FCAT_1, FCAT_2 )VALUES ( 'Apple','Banana')
INSERT INTO #tmp( FCAT_1, FCAT_2 )VALUES ( 'Bike','Car')

This query return JSON.

SELECT * FROM #tmp
FOR json AUTO;

Output: [{"FCAT_1":"Apple","FCAT_2":"Banana"},{"FCAT_1":"Bike","FCAT_2":"Car"}]

This query return json with ROOT Name.

Select *
from #tmp
For JSON PATH, ROOT('Result')

Output: {"Result":[{"FCAT_1":"Apple","FCAT_2":"Banana"},{"FCAT_1":"Bike","FCAT_2":"Car"}]}

This query return XML.

Select * from #tmp
For XML PATH

Output: <row><FCAT_1>Apple</FCAT_1><FCAT_2>Banana</FCAT_2></row><row><FCAT_1>Bike</FCAT_1><FCAT_2>Car</FCAT_2></row>