JSON (JavaScript Object Notation) is a popular data format used for storing and exchanging data. With the introduction of JSON support in SQL Server 2016, developers now have the ability to work with JSON documents directly within the database.

In this blog post, we will explore the similarities between using JSON and the FORXML constructs in SQL Server, as well as discuss a restriction when using the FOR JSON clause in a DML statement like UPDATE.

When working with SQL Server for many years, it’s helpful to have a reference point when learning new concepts. Understanding the similarities between JSON and FORXML can accelerate the learning process and make it easier to work with these new concepts.

One of the restrictions of using the FOR JSON clause in SQL Server 2016 is that it cannot be used in a DML statement like UPDATE. If you attempt to use the FOR JSON clause in an UPDATE statement, you will receive an error message similar to the following:

Msg 13602, Level 16, State 1, Line 7
The FOR JSON clause is not allowed in an UPDATE statement.

Here is an example of the code that would cause this error:

UPDATE MyJSONTable
SET col = 1
FOR JSON AUTO

As you can see from the error message, the FOR JSON clause is not supported in an UPDATE statement. This restriction is similar to the one found when using the FOR XML clause in previous versions of SQL Server.

For completeness, let’s also consider the error message you would receive when using the FOR XML clause in an UPDATE statement:

Msg 6819, Level 16, State 1, Line 7
The FOR XML clause is not allowed in an UPDATE statement.

It’s interesting to note that many of the features of the FOR JSON clause in SQL Server 2016 are similar to how the FOR XML clause used to work in previous versions. However, it’s unlikely that anyone had a requirement for getting an output clause as JSON. If you can think of a scenario where this would be useful, please let me know in the comments.

Working with JSON documents in SQL Server opens up new possibilities for storing and manipulating data. Understanding the similarities and differences between JSON and FORXML can help you leverage this new capability effectively.