SQL 2016: Send to JSON
Author: jason
Date: 2019-01-17 11:12:48
Category: Technical

In SQL 2016 you can natively send data out as JSON, which is pretty nice since quite a few languages want to talk JSON

https://www.mssqltips.com/sqlservertip/4014/json-support-in-sql-server-2016/

https://www.mssqltips.com/sqlservertip/4041/including-null-values-in-json-output-in-sql-server-2016/

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017

Code


DROP TABLE IF EXISTS #Temp;
CREATE TABLE #Temp ([Name] VARCHAR(25), [Description] VARCHAR(255));
INSERT #Temp VALUES ('Apple','Red'), ('Apple','Green'), ('Plum','Purple');
DECLARE @One AS VARCHAR(255) = 'Apple';
DECLARE @Two AS VARCHAR(255) = 'Red';
DECLARE @Json AS VARCHAR(MAX) = '';

SET @Json = (SELECT @One AS [Name], @Two AS [Description] FOR JSON PATH);
SELECT @Json AS JsonOutput;

SET @Json = (SELECT t.* FROM #Temp t FOR JSON PATH, ROOT('Data'));
SELECT @Json AS JsonOutput;

SET @Json = (SELECT t.* FROM #Temp t FOR JSON PATH);
SELECT @Json AS JsonOutput;

SELECT t.Name FROM #Temp t FOR JSON PATH;







jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009