Generate Hierarchical json in sql server without recursive function

Question

I have to build a tree as is in the followed link like this http://bl.ocks.org/robschmuecker/7880033,

I need to generate hierarchical json data in sqlserver database, i have tried using recursive function but as recursive function has max limit32 in sql server i cant contunue with the function, where in i have a very huge amount of data same as above URL, the tree is complete dynamic and below is my table structure

CREATE TABLE #dndclasses
(
   id         INT  IDENTITY PRIMARY KEY,
   parent_id  INT,
   name       TEXT
);



INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (1, 0, N'Tom')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (2, 0, N'Josh')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (3, 1, N'Mike')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (4, 1, N'John')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (5, 2, N'Pam')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (6, 2, N'Mary')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (7, 3, N'James')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (8, 3, N'Sam')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (9, 4, N'Simon')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (10, 4, N'QQom')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (11, 4, N'QQosh')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (12, 6, N'QQike')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (13, 6, N'QQohn')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (14, 7, N'QQam')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (15, 7, N'QQary')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (16, 8, N'QQames')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (17, 8, N'QQam')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (18, 4, N'QQimon')

Any help is really appreciated and thanks in advance

Basically i almost need the hierarchical query like in

https://tapoueh.org/blog/2018/01/exporting-a-hierarchy-in-json-with-recursive-queries/

But the above is in postgre sql, i have to achieve the same in sql server

Expected Output

[{
  "id": 1,
  "parent_id": 0,
  "name": "Tom",
  "Children": [{
    "id": 3,
    "parent_id": 1,
    "name": "Mike",
    "Children": [{
      "id": 7,
      "parent_id": 3,
      "name": "James",
      "Children": [{
        "id": 14,
        "parent_id": 7,
        "name": "QQam"
      }, {
        "id": 15,
        "parent_id": 7,
        "name": "QQary"
      }]
    }, {
      "id": 8,
      "parent_id": 3,
      "name": "Sam",
      "Children": [{
        "id": 16,
        "parent_id": 8,
        "name": "QQames"
      }, {
        "id": 17,
        "parent_id": 8,
        "name": "QQam"
      }]
    }]
  }, {
    "id": 4,
    "parent_id": 1,
    "name": "John",
    "Children": [{
      "id": 9,
      "parent_id": 4,
      "name": "Simon"
    }, {
      "id": 10,
      "parent_id": 4,
      "name": "QQom"
    }, {
      "id": 11,
      "parent_id": 4,
      "name": "QQosh"
    }, {
      "id": 18,
      "parent_id": 4,
      "name": "QQimon"
    }]
  }]
}, {
  "id": 2,
  "parent_id": 0,
  "name": "Josh",
  "Children": [{
    "id": 5,
    "parent_id": 2,
    "name": "Pam"
  }, {
    "id": 6,
    "parent_id": 2,
    "name": "Mary",
    "Children": [{
      "id": 12,
      "parent_id": 6,
      "name": "QQike"
    }, {
      "id": 13,
      "parent_id": 6,
      "name": "QQohn"
    }]
  }]
}]

Answer 1

The recursive approach is the best practice to resolve your requirements; BUT, this query that I share bellow works only with three leves of hierarchy (based on nature of your exampled data). Although, if your tree have more than three nested objects, you need to resort an automatically recursive approach:

SELECT Parent.id, Parent.name,
    (SELECT c.id, c.name,
        (SELECT cc.id, cc.name
         FROM #dndclasses cc
         WHERE cc.parent_id = c.id FOR JSON PATH) as ChildOfChilds
     FROM #dndclasses c
     WHERE c.parent_id = Parent.id FOR JSON PATH) as Childs
FROM #dndclasses Parent
WHERE Parent.parent_id = 0 FOR JSON PATH

Please, if possible, give us a feed-back.