Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- declare @pJSON varchar(max) = '
- {
- "School": "MiddleSchool",
- "Password": "SchoolPassword",
- "Attributes": [
- {
- "Type": "Exam",
- "Value": "1"
- },
- {
- "Type": "Class",
- "Value": "11b"
- },
- {
- "Type": "Math",
- "Value": [
- {
- "ExamDate": "2019-01-01",
- "Points": 100,
- "Grade": 10,
- "Notes": "Good"
- }
- ]
- }
- ]
- } '
- select ExamDate, Points, Grade, Notes
- from OPENJSON(@pJSON, N'$.Attributes[2].Value')
- cross apply openjson ([Value])
- with
- (
- ExamDate date,
- Points int,
- Grade int,
- Notes varchar(max)
- ) as [value]
- select ExamDate, Points, Grade, Notes
- from OPENJSON(JSON_QUERY(@pJSON, N'$.Attributes'))
- with
- (
- ExamDate date N'$.Value[0].ExamDate',
- Points int N'$.Value[0].Points',
- Grade int N'$.Value[0].Grade',
- Notes varchar(max) N'$.Value[0].Notes'
- ) as [value]
- WHERE ExamDate IS NOT NULL;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement