SQL Server SELECT to JSON function

by

Starting from SQL Server 2016 you can use for json:

declare @t table(id int, name nvarchar(max), active bit)
insert @t values (1, 'Bob Jones', 1), (2, 'John Smith', 0)

select id, name, active
from @t
for json auto

With older versions of SQL Server you can use for xml path, e.g.:

select '[' + STUFF((
        select 
            ',{"id":' + cast(id as varchar(max))
            + ',"name":"' + name + '"'
            + ',"active":' + cast(active as varchar(max))
            +'}'

        from @t t1
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') + ']'

Output:

[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]

More Related Contents:

  1. SQL: How can I get the value of an attribute in XML datatype?
  2. Concatenate grouped rows
  3. How can I query a value in SQL Server XML column
  4. Convert Xml to Table SQL Server
  5. When can I save JSON or XML data in an SQL Table
  6. Finding node order in XML document in SQL Server
  7. SQL Server FOR XML Path make repeating nodes
  8. I dont know sql syntax for my table [closed]
  9. Get top 1 row of each group
  10. Convert Month Number to Month Name Function in SQL
  11. Multiple INSERT statements vs. single INSERT with multiple VALUES
  12. Limit on the WHERE col IN (…) condition
  13. Removing leading zeroes from a field in a SQL statement
  14. Generate a resultset of incrementing dates in TSQL
  15. Getting result of dynamic SQL into a variable for sql-server
  16. Better techniques for trimming leading zeros in SQL Server?
  17. Common Table Expression, why semicolon?
  18. T-SQL split string based on delimiter
  19. Using ISNULL vs using COALESCE for checking a specific condition?
  20. SQL Select Upcoming Birthdays
  21. How can I include null values in a MIN or MAX?
  22. Call dynamic SQL from function
  23. Stored procedure that exports data into csv files only exports to one file
  24. Get Start and End Date from Week Number SQL Server
  25. Get the last day of the month in SQL
  26. What are the use cases for selecting CHAR over VARCHAR in SQL?
  27. SQL take just the numeric values from a varchar
  28. What does collation mean?
  29. Convert bigint to datetime
  30. Replacing NULL and empty string within Select statement

Leave a Comment