I am trying to use it in my SQL Query exactly like it's shown in the link below on MSDN. The keyword JSON does not turn blue and gives error
Incorrect syntax near 'JSON'
What's wrong with it?
EDIT: I'm testing it for SQL Server 2014. The query is
SELECT * FROM food FOR JSON AUTO
FOR JSON AUTO is available from SQL SERVER 2016. If you are using SQL SERVER 2014 or former, then you can use the following approach:
SELECT '['+ STUFF((
SELECT ',{"Col1":"' + CAST(t1.name AS NVARCHAR(MAX)) + '",'+
+'"Col2":"'+CAST(t1.database_id AS NVARCHAR(MAX)) + '"}'
FROM Food t1 FOR XML PATH(''), TYPE
).value('.', 'varchar(max)'),1,1,''
) + ']';
You can validate the output using various online tools such as JSON LINT to make sure that the result is valid json-formatted result.
Update:
Here is the screenshot of the code and result:
Thank you Vahid for the query very handy. Just a quick modification if the record have '\' in their value JSON will fail so modified the query to accommodate that
SELECT '['+ STUFF((
SELECT ',{"Col1":"' + REPLACE(CAST(t1.name AS NVARCHAR(MAX)),'\','\\') + '",'+
+'"Col2":"' + REPLACE(CAST(t1.value AS NVARCHAR(MAX)),'\','\\') + '"}'
FROM Food t1 FOR XML PATH(''), TYPE
).value('.', 'varchar(max)'),1,1,''
) + ']';
JSON AUTO Only use in SQL Server 2016.
