CODEWITHSUNDEEP
sqljsonsql-serversql-server-2008
alax
alax

Reputation: 73

How to convert data to json format in SQL Server 2008?

I am using SQL Server 2008 and want to convert table data into json format. Can I convert it directly through firing query?

Upvotes: 7

Views: 46786

Answers (3)

Yury
Yury

Reputation: 781

The above solutions seem unnecessarily complicated; starting with 2008, SQL Server supports the following syntax (answer from DBA StackExchange)

SELECT * FROM dbo.x FOR JSON AUTO;

The above query returns a single JSON-formatted column that looks like this (assuming dbo.x contains columns col1~col4of corresponding types):

[{"col1":"val1","col2":"val2","col3":5,"col4":"2019-02-11"}]

More details/options here: https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server

Upvotes: -3

Ahliana
Ahliana

Reputation: 86

I have created a stored procedure that can take your table and output JSON. You can find it at

Once you run the stored procedure, you can output your table by making a call like the following:

EXEC SQLTableOrViewToJSON 'MyTable', 'C:\WhereIStowMyJSON\'

Upvotes: 5

Jovan MSFT
Jovan MSFT

Reputation: 14660

Built in support for formatting query results is added in SQL Server 2016 and it will be available in Azure Database. In older versions you would need to use CLR or some heavy TSQL like:

Upvotes: 2

Related Questions