By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab( JsonData VARCHAR(8000) );
INSERT INTO tab VALUES('{
"S": [ { "Name": "0219 Project Methodology - Allergies",
"SP": [ { "ID": 1,"Name": "Test1",
"TP": [ { "TID": 11, "TName": "TT1" }, { "TID": 12, "TName": "TT2" }] },
{ "ID": 2,"Name": "Test2",
"TP": [ { "TID": 13, "TName": "TT3" }, { "TID": 14, "TName": "TT3" }] }
]
}
]}');
1 rows affected
SELECT S3.TID, S3.TName
FROM tab
CROSS APPLY OPENJSON(JsonData)
WITH ( S nvarchar(max) '$.S' AS JSON) AS S0
CROSS APPLY OPENJSON (S0.S)
WITH (
SP nvarchar(max) '$.SP' AS JSON ) S1
CROSS APPLY OPENJSON (S1.SP)
WITH (
TP nvarchar(max) '$.TP' AS JSON ) S2
CROSS APPLY OPENJSON (S2.TP)
WITH (
TID nvarchar(500) '$.TID',
TName nvarchar(500) '$.TName' ) S3
| TID | TName |
|---|---|
| 11 | TT1 |
| 12 | TT2 |
| 13 | TT3 |
| 14 | TT3 |