SQL Server 2016 SSMS Json Formatting

Question

I am working with JSON in SSMS in SQL Server 2016. Is there any way to get around that the results of a FOR JSON statement are presented as an XML column?

Are there any settings changes, additional components, external tools, etc. or even hacks to make this less painful?

In my ideal world, clicking on a JSON column (or an XML column for that matter) would open in my text editor of choice, such as Visual Studio Code, with SSMS knowing that the string in the column is JSON and saving it to file with a .json extension.

Answer 1

Azure Data Studio will do most of what you want.

Run your query:

Clicking on the RESULTS will open a new tab with formatted JSON:

Answer 2

I just want to add that the "MsSql Extension" for VS Code is really starting to be very useful with the kind of JSON support that you would expect.

It doesn't replace SSMS but works for basic SQL work and when you click on a JSON column it actually opens in formatted JSON! The added bonus is that I usually have VS Code open for other things anyway these days.