SQL Server - "for json path" statement does not return more than 2984 lines of JSON string
As I wrote in my comment to the OP, this is probably due to SSMS has a limit of how many characters to display in a column in the result grid. It has no impact on the actual result, e.g. the result has all data, it is just that SSMS doesn't display it all.
To fix this, you can increase the number of characters SSMS retrieves:
I would not recommend that - "how long is a piece of string", but instead select the result into a nvarchar(max)
variable, and PRINT
that variable. That should give you the whole text.
Hope this helps!