Return to Answer

##Something similar for XML

Something similar for XML

##Something similar for XML

Here is a similar answer about how to read an unknown XML.

WITH recCTE AS
(
    SELECT ID
          ,NestLevel   = 1                                                            
          ,SortString  = CAST(N'0'                          COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX)) 
                                                            
          ,JsonPath    = CAST(N'$'                          COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
          ,JsonKey     = CAST(N'$'                          COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX)) 
          ,JsonValue   = CAST(AnyJSON                       COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX)) 
          ,JsonType    = CAST(CASE WHEN LEFT(TRIM(AnyJSON),1)=N'[' THEN 4 ELSE 0 END AS TINYINT)
          ,NestedJSON  = CAST(CASE WHEN ISJSON(AnyJSON)=1 
                                   THEN AnyJSON 
                                   ELSE NULL END            COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX)) 
    FROM @table t

    UNION ALL

    SELECT r.ID
          ,r.NestLevel+1
          ,CAST(CONCAT(r.SortString,STR(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),5)) COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))

          ,CAST(CONCAT(r.JsonPath, CASE WHEN r.JsonType=4 --<-- see the docs for OPENJSON()
                                        THEN CONCAT('[',A.[key],']') 
                                        ELSE '.' + A.[key] END)                       COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
          ,CAST(A.[key]                                                               COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
          ,CAST(r.JsonValue                                                           COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
          ,A.[type] 
          ,CAST(A.[value]                                                             COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
    FROM recCTE r
    CROSS APPLY OPENJSON(r.NestedJSON) A
    WHERE ISJSON(r.NestedJSON)=1
)
SELECT ID
      ,JsonPath
      ,JsonKey
      ,NestedJSON AS JsonValue
      ,SortString --<-- just to illustrate the sorting, not needed in the output
FROM recCTE 
WHERE ISJSON(NestedJSON)=0
ORDER BY ID,SortString;

WITH recCTE AS
(
    SELECT ID
          ,NestLevel   = 0 
          ,ObjectIndex = CAST(1 AS bigint)                                                          
          ,SortString  = CAST(N'sort'                       COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX)) 

          ,JsonPath    = CAST(N'$'                          COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
          ,JsonKey     = CAST(N'$'                          COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX)) 
          ,JsonValue   = CAST(AnyJSON                       COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX)) 
          ,JsonType    = CAST(CASE WHEN LEFT(TRIM(AnyJSON),1)=N'[' THEN 4 ELSE 0 END AS TINYINT)
          ,NestedJSON  = CAST(CASE WHEN ISJSON(AnyJSON)=1 
                                   THEN AnyJSON 
                                   ELSE NULL END            COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX)) 
    FROM @table t

    UNION ALL

    SELECT r.ID
          ,r.NestLevel+1
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
          ,CAST(CONCAT(r.SortString,REPLACE(STR(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),5),' ','0')) COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))

          ,CAST(CONCAT(r.JsonPath, CASE WHEN r.JsonType=4 --<-- see the docs for OPENJSON()
                                        THEN CONCAT('[',A.[key],']') 
                                        ELSE '.' + A.[key] END)                       COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
          ,CAST(A.[key]                                                               COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
          ,CAST(r.JsonValue                                                           COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
          ,A.[type] 
          ,CAST(A.[value]                                                             COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
    FROM recCTE r
    CROSS APPLY OPENJSON(r.NestedJSON) A
    WHERE ISJSON(r.NestedJSON)=1
)
SELECT ID
      ,NestLevel
      ,ObjectIndex
      ,JsonPath
      ,JsonKey
      ,NestedJSON AS JsonValue
      ,SortString --<-- just to illustrate the sorting, not needed in the output
FROM recCTE 
WHERE ISJSON(NestedJSON)=0
ORDER BY ID,SortString;