Not everytime the defaults will work when working with databases like SQL Server. You will want to do your own customization and there would be multiple learnings of using the same. Here is a classic example. When working with the FOR JSON PATH, I got the below error:
Msg 13603, Level 16, State 1, Line 1
Property ‘.SQLAuth’ cannot be generated in JSON output due to invalid character in the column name or alias. Column name or alias that contains ‘..’, starts or ends with ‘.’ is not allowed in query that has FOR JSON clause.
This error was generated by the following command shown in the image below:
This is when I realized the mistake I had made. An error is reported when illegal characters are found in the column or column alias used in FOR JSON PATH. The name of one of the column starts or ends with dot.
Remedy: as a corrective action, I fixed column alias to conform to FOR JSON PATH rules.
I wanted to see what the error would be using the same mistake with XML PATH. I wrote the same query as shown:
SELECT 1 as '.SQLAuth',2 as 'SQLAuth..Pinal' FOR XML PATH
Msg 6850, Level 16, State 1, Line 1
Column name ‘.SQLAuth’ contains an invalid XML identifier as required by FOR XML; ‘.'(0x002E) is the first character at fault.
I can surely see it is similar but slightly different. I am not sure if Microsoft will change this to something similar to JSON error message. But this is good enough in my opinion.
Reference: Pinal Dave (https://blog.sqlauthority.com)