SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (nodeName varchar(50), name varchar(50), parentname varchar(50), type varchar(50), code varchar(50), label varchar(50), version varchar(50));
insert into t(nodeName,name,parentname,type,code,label,version)
values

('NODE NAME 1','NODE NAME 1','','type3','N1','Node name 1','v1.0'),
('NODE NAME 2.1','NODE NAME 2.1','NODE NAME 1','type1','N2.1','Node name 2.1','v1.0'),
('NODE NAME 2.2','NODE NAME 2.2','NODE NAME 1','type1','N2.2','Node name 2.2','v1.0'),
('NODE NAME 2.3','NODE NAME 2.3','NODE NAME 1','type1','N2.3','Node name 2.3','v1.0'),
('NODE NAME 2.4','NODE NAME 2.4','NODE NAME 1','type1','N2.4','Node name 2.4','v1.0'),
('NODE NAME 2.5','NODE NAME 2.5','NODE NAME 1','type1','N2.5','Node name 2.5','v1.0'),
('NODE NAME 3.1','NODE NAME 3.1','NODE NAME 2.1','type2','N3.1','Node name 3.1','v1.0'),
('NODE NAME 3.2','NODE NAME 3.2','NODE NAME 2.1','type2','N3.2','Node name 3.2','v1.0'),
('NODE NAME 3.3','NODE NAME 3.3','NODE NAME 2.3','type1','N3.3','Node name 3.3','v1.0'),
('NODE NAME 3.4','NODE NAME 3.4','NODE NAME 2.3','type1','N3.4','Node name 3.4','v1.0'),
('NODE NAME 3.5','NODE NAME 3.5','NODE NAME 2.4','type2','N3.5','Node name 3.5','v1.0'),
('NODE NAME 3.6','NODE NAME 3.6','NODE NAME 2.5','type2','N3.6','Node name 3.6','v1.0'),
('NODE NAME 4.1','NODE NAME 4.1','NODE NAME 3.3','type4','N4.1','Node name 4.1','v1.0'),
('NODE NAME 4.2','NODE NAME 4.2','NODE NAME 3.4','type4','N4.2','Node name 4.2','v1.0'),
('NODE NAME 5.1','NODE NAME 5.1','NODE NAME 4.1','type4','N5.1','Node name 5.1','v1.0'),
('NODE NAME 5.2','NODE NAME 5.2','NODE NAME 4.1','type4','N5.2','Node name 5.2','v1.0'),
('NODE NAME 6.1','NODE NAME 6.1','NODE NAME 5.2','type4','N6.1','Node name 6.1','v1.0'),
('NODE NAME 6.2','NODE NAME 6.2','NODE NAME 5.2','type4','N6.2','Node name 6.2','v1.0');
18 rows affected

CREATE OR ALTER FUNCTION dbo.GetJson ( @name varchar(50) )
RETURNS nvarchar(max)
WITH SCHEMABINDING
-- need to create it first if you want to schema-bind with recursion
AS BEGIN RETURN NULL; END;
CREATE OR ALTER FUNCTION dbo.GetJson ( @name varchar(50) )
RETURNS nvarchar(max)
WITH SCHEMABINDING
AS
BEGIN
  RETURN (
    SELECT
      nodeName,
      name,
      parentname,
      type,
      code,
      label,
      version,
      JSON_QUERY(dbo.GetJson(name)) AS children
    FROM dbo.t
    WHERE t.parentname = @name
    FOR JSON PATH
  );
END;
SELECT dbo.GetJson('');


(No column name)


[{"nodeName":"NODE NAME 1","name":"NODE NAME 1","parentname":"","type":"type3","code":"N1","label":"Node name 1","version":"v1.0","children":[{"nodeName":"NODE NAME 2.1","name":"NODE NAME 2.1","parentname":"NODE NAME 1","type":"type1","code":"N2.1","label":"Node name 2.1","version":"v1.0","children":[{"nodeName":"NODE NAME 3.1","name":"NODE NAME 3.1","parentname":"NODE NAME 2.1","type":"type2","code":"N3.1","label":"Node name 3.1","version":"v1.0"},{"nodeName":"NODE NAME 3.2","name":"NODE NAME 3.2","parentname":"NODE NAME 2.1","type":"type2","code":"N3.2","label":"Node name 3.2","version":"v1.0"}]},{"nodeName":"NODE NAME 2.2","name":"NODE NAME 2.2","parentname":"NODE NAME 1","type":"type1","code":"N2.2","label":"Node name 2.2","version":"v1.0"},{"nodeName":"NODE NAME 2.3","name":"NODE NAME 2.3","parentname":"NODE NAME 1","type":"type1","code":"N2.3","label":"Node name 2.3","version":"v1.0","children":[{"nodeName":"NODE NAME 3.3","name":"NODE NAME 3.3","parentname":"NODE NAME 2.3","type":"type1","code":"N3.3","label":"Node name 3.3","version":"v1.0","children":[{"nodeName":"NODE NAME 4.1","name":"NODE NAME 4.1","parentname":"NODE NAME 3.3","type":"type4","code":"N4.1","label":"Node name 4.1","version":"v1.0","children":[{"nodeName":"NODE NAME 5.1","name":"NODE NAME 5.1","parentname":"NODE NAME 4.1","type":"type4","code":"N5.1","label":"Node name 5.1","version":"v1.0"},{"nodeName":"NODE NAME 5.2","name":"NODE NAME 5.2","parentname":"NODE NAME 4.1","type":"type4","code":"N5.2","label":"Node name 5.2","version":"v1.0","children":[{"nodeName":"NODE NAME 6.1","name":"NODE NAME 6.1","parentname":"NODE NAME 5.2","type":"type4","code":"N6.1","label":"Node name 6.1","version":"v1.0"},{"nodeName":"NODE NAME 6.2","name":"NODE NAME 6.2","parentname":"NODE NAME 5.2","type":"type4","code":"N6.2","label":"Node name 6.2","version":"v1.0"}]}]}]},{"nodeName":"NODE NAME 3.4","name":"NODE NAME 3.4","parentname":"NODE NAME 2.3","type":"type1","code":"N3.4","label":"Node name 3.4","version":"v1.0","children":[{"nodeName":"NODE NAME 4.2","name":"NODE NAME 4.2","parentname":"NODE NAME 3.4","type":"type4","code":"N4.2","label":"Node name 4.2","version":"v1.0"}]}]},{"nodeName":"NODE NAME 2.4","name":"NODE NAME 2.4","parentname":"NODE NAME 1","type":"type1","code":"N2.4","label":"Node name 2.4","version":"v1.0","children":[{"nodeName":"NODE NAME 3.5","name":"NODE NAME 3.5","parentname":"NODE NAME 2.4","type":"type2","code":"N3.5","label":"Node name 3.5","version":"v1.0"}]},{"nodeName":"NODE NAME 2.5","name":"NODE NAME 2.5","parentname":"NODE NAME 1","type":"type1","code":"N2.5","label":"Node name 2.5","version":"v1.0","children":[{"nodeName":"NODE NAME 3.6","name":"NODE NAME 3.6","parentname":"NODE NAME 2.5","type":"type2","code":"N3.6","label":"Node name 3.6","version":"v1.0"}]}]}]