Post
Ask Question

Get JSON using SQL Server 2016 or Azure SQL Database

Here I will create one temp table for save data.
  1. CREATE TABLE #tmp(  
  2. [FCAT_1] [varchar](500) NULL,  
  3. [FCAT_2] [varchar](500) NULL  
  4.   
  5. )  
  6.    
  7. INSERT INTO #tmp( FCAT_1, FCAT_2 )VALUES ( 'Apple','Banana')  
  8. INSERT INTO #tmp( FCAT_1, FCAT_2 )VALUES ( 'Bike','Car') 
This query return JSON.
  1. SELECT * FROM #tmp  
  2. FOR json AUTO; 
Output: [{"FCAT_1":"Apple","FCAT_2":"Banana"},{"FCAT_1":"Bike","FCAT_2":"Car"}]
 
This query return json with ROOT Name.
  1. Select *  
  2. from #tmp  
  3. For JSON PATH, ROOT('Result') 
Output: {"Result":[{"FCAT_1":"Apple","FCAT_2":"Banana"},{"FCAT_1":"Bike","FCAT_2":"Car"}]}
 
This query return XML.
  1. Select * from #tmp  
  2. 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>