SHARE
TWEET

Untitled

a guest Jun 26th, 2019 73 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. declare @pJSON varchar(max) = '
  2. {
  3.     "School": "MiddleSchool",
  4.     "Password": "SchoolPassword",
  5.     "Attributes": [
  6.         {
  7.             "Type": "Exam",
  8.             "Value": "1"
  9.         },
  10.         {
  11.             "Type": "Class",
  12.             "Value": "11b"
  13.         },
  14.         {
  15.             "Type": "Math",
  16.             "Value": [
  17.                {
  18.                     "ExamDate": "2019-01-01",
  19.                     "Points": 100,
  20.                     "Grade": 10,
  21.                     "Notes": "Good"
  22.                 }
  23.             ]
  24.         }  
  25.     ]  
  26. }   '
  27.  
  28.  
  29. select ExamDate, Points, Grade, Notes  
  30. from OPENJSON(@pJSON, N'$.Attributes[2].Value')    
  31. cross apply openjson ([Value])  
  32. with    
  33. (    
  34.   ExamDate date,    
  35.   Points int,    
  36.   Grade int,    
  37.   Notes varchar(max)    
  38. ) as [value]
  39.      
  40. select ExamDate, Points, Grade, Notes  
  41. from OPENJSON(JSON_QUERY(@pJSON, N'$.Attributes'))
  42. with    
  43. (    
  44.   ExamDate date      N'$.Value[0].ExamDate',    
  45.   Points int         N'$.Value[0].Points',    
  46.   Grade int          N'$.Value[0].Grade',    
  47.   Notes varchar(max) N'$.Value[0].Notes'
  48. ) as [value]
  49. WHERE ExamDate IS NOT NULL;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top