The following is formatted in JSON
["wclass",
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}etc..
},
{ "student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
},
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}
}]
This is a valid JSON according to RFC 4627.
json_01
However when you use this in SQL Server to look at the JSON using the OpenJSON, for example, you will get this error:
json_00
The reason for this error:
Msg 103, Level 15, State 4, Line 2
The identifier that starts with "wclass"...
lies in the fact that SQL Server string starts with a single quote and therefore you need to provide this declaration:
declare @json nvarchar(Max)
set @json=
'["wclass",
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
},
{"student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
},
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}
}]'
When you do this the error goes away as shown:
json_02
The character count in @json is also important as you see in this SQL query:
json_03
The answer is that RFC 4627 validation requires a string to start with a double quote("), but the SQL Server's JSON validation requires the JSON to begin with a single quote(') as we saw in this post.
["wclass",
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}etc..
},
{ "student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
},
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}
}]
This is a valid JSON according to RFC 4627.
json_01
However when you use this in SQL Server to look at the JSON using the OpenJSON, for example, you will get this error:
json_00
The reason for this error:
Msg 103, Level 15, State 4, Line 2
The identifier that starts with "wclass"...
lies in the fact that SQL Server string starts with a single quote and therefore you need to provide this declaration:
declare @json nvarchar(Max)
set @json=
'["wclass",
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
},
{"student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
},
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}
}]'
When you do this the error goes away as shown:
json_02
The character count in @json is also important as you see in this SQL query:
json_03
The answer is that RFC 4627 validation requires a string to start with a double quote("), but the SQL Server's JSON validation requires the JSON to begin with a single quote(') as we saw in this post.
No comments:
Post a Comment