7

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
alax
  • 73
  • 1
  • 1
  • 6
  • 1
    There isn't a SQL Server 2007. But assuming yyyy<2016 then there is no inbuilt functionality to do this. – Martin Smith Dec 19 '15 at 17:44
  • 1
    No, SQL Server 2008 doesn't have any support for handling JSON directly. SQL Server **2016** will have full support for JSON - until then, you need to do this in your logic layer (in whatever programming language you're comfortable with) – marc_s Dec 19 '15 at 17:53
  • 1
    The logic for doing this is relatively easy. It is also relatively straightforward to emit json from a client SQL application. If you MUST do it directly from SQL, you could write a stored procedure or an extended stored procedure written in C/C++ or C# (warning - a lot of shops legislate against this and for good reason). I think the client program is the way to go and for what it's worth also think that SQL Server is best reserved for data storage, query, and manipulation - not formatting and presentation to the user, which is really the forte of the client program. Good Luck. – Ken Clement Dec 19 '15 at 19:07
  • Clarification: it is the extended stored procedures that is outlawed by many shops. a sproc is a bit more involved though than a client program. – Ken Clement Dec 19 '15 at 19:09
  • Possible duplicate of [SQL Server SELECT to JSON function](http://stackoverflow.com/questions/6818441/sql-server-select-to-json-function) – Frédéric Feb 26 '16 at 12:42

3 Answers3

5

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\'
Hadi
  • 36,233
  • 13
  • 65
  • 124
Ahliana
  • 86
  • 1
  • 6
2

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
Jovan MSFT
  • 13,232
  • 4
  • 40
  • 55
-3

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

Yury
  • 722
  • 7
  • 14