（Microsoft SQL）如何将其层次相关的子项目作为JSON值获得项目？

Question

我有一张桌子，

CREATE TABLE dbo.items (
    item_id INT PRIMARY KEY,
    name VARCHAR,
);

然后有一个桌子，套件，其中显示了哪些项目是子项目。

CREATE TABLE dbo.kits (
    parent_item_id INT
    child_item_id INT
);

INSERT INTO items (id, name)
VALUES 
(1, "Rainy day kit"),
(2, "Beach day kit"),
(3, "Umbrella"),
(4, "Jacket"),
(5, "Swimsuit"),
(6, "Sunscreen"),
(7, "Rainy Beach day kit")

INSERT INTO kits (parent_item_id, child_item_id)
VALUES 
(1, 3),
(1, 4),
(2, 5),
(2, 6),
(7, 1),
(7, 2);

我想和他们的孩子以JSON格式获得一些物品。此查询非常适合将其儿童物品获取物品，

SELECT i.item_id, i.name, 
   (SELECT i2.item_id, i2.name 
    FROM items i2 
    INNER JOIN kits k on k.child_item_id = i2.item_id
    WHERE k.parent_item_id = i.item_id
    FOR JSON PATH
    )
FROM items i
WHERE i.item_id = 1

item_id, name, kit_children
1, "Rainy day kit", [{"item_id":3, "name":"Umbrella"},{"item_id":4, "name":"Jacket"}]

但是如果我想递归地进行更深入的研究，这将行不通。有人知道一种递归获取儿​​童物品的方法吗？ 这样的查询可以返回这样的结果：

item_id, name, kit_children
7, "Rainy Beach day kit", [{"item_id":1, "name":"Rainy day kit", "kit_children":[{"item_id":3, "name":"Umbrella"},{"item_id":4, "name":"Jacket"}]}, {"item_id":2, "name":"Beach day kit", "kit_children":[{"item_id":5, "name":"Swimsuit"},{"item_id":6, "name":"Sunscreen"}]}]