SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE [dbo].[CategoryMaster](
    [CategoryId] [int] NOT NULL,
    [ParentId] [int] NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_CategoryMaster] PRIMARY KEY CLUSTERED 
(
    [CategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (1, NULL, N'Toys & Games')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (2, 1, N'Art And Crafts')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (3, 1, N'Baby & Toddler Toys')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (4, 1, N'Bikes, Trikes & Ride-Ons')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (5, 2, N'Aprons & Smocks')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (6, 2, N'Blackboards & Whiteboards')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (7, 2, N'Clay & Dough')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (8, 1, N'Pretend Play')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (9, 8, N'Kitchen Toys')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (10, 9, N'Cooking Appliances')
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (11, 9, N'Cookware')
ALTER TABLE [dbo].[CategoryMaster]  WITH CHECK ADD  CONSTRAINT [FK_CategoryMaster_CategoryMaster] FOREIGN KEY([ParentId])
REFERENCES [dbo].[CategoryMaster] ([CategoryId])
ALTER TABLE [dbo].[CategoryMaster] CHECK CONSTRAINT [FK_CategoryMaster_CategoryMaster]

11 rows affected

CREATE FUNCTION Create_Json(@CategoryId INT, @IsRoot INT) 
RETURNS VARCHAR(MAX)
BEGIN 
    DECLARE @Json NVARCHAR(MAX) = '{}', @Name NVARCHAR(MAX) , @Children NVARCHAR(MAX)

    SET @Json =  (SELECT P.[Name],JSON_QUERY(dbo.Create_Json(P.CategoryId, 2) ) AS Children 
                    FROM [dbo].[CategoryMaster] AS P  
                    WHERE P.ParentId = @CategoryId 
                    FOR JSON AUTO);
    IF(@IsRoot = 1) 
    BEGIN
       SELECT @Name = P.[Name] FROM [dbo].[CategoryMaster] AS P WHERE P.CategoryId = @CategoryId
       SET @Json =   '"result": {"Name":"' + @Name + '","Children":' + CAST(@Json AS NVARCHAR(MAX)) + '}'
       SET @IsRoot = 0
    END
    RETURN @Json 
END
select dbo.Create_Json(1, 1)


(No column name)


"result": {"Name":"Toys & Games","Children":[{"Name":"Art And Crafts","Children":[{"Name":"Aprons & Smocks"},{"Name":"Blackboards & Whiteboards"},{"Name":"Clay & Dough"}]},{"Name":"Baby & Toddler Toys"},{"Name":"Bikes, Trikes & Ride-Ons"},{"Name":"Pretend Play","Children":[{"Name":"Kitchen Toys","Children":[{"Name":"Cooking Appliances"},{"Name":"Cookware"}]}]}]}