drop table if exists #eav, #tmp, #tmp2;
Create table #eav ([id] nvarchar(100), [field] nvarchar(100), [value] nvarchar(100))

insert into #eav
	Values   ('0001', 'root1', Null)
			,('0001/0001', 'field1', '1')
			,('0001/0002', 'field2', '2')
			,('0001/0002/0001', 'field3', '3')
			,('0001/0002/0001/0001', 'field5', '5')
			,('0001/0002/0002', 'field4', '4');

update tmp
   set [id] = '/'+xx.val
  from #eav tmp
      cross apply (select x.data.value('.', 'varchar(max)') from (select cast(cast(t.value as int) as varchar) + '/' from string_split(tmp.id, '/') t for xml path(''), type) x(data)) xx (val);

select cast(id as hierarchyid) as rid, cast(id as hierarchyid).GetAncestor(1) as parentid, * into #tmp from #eav;

alter table #tmp add [rowid] int identity(1,1);


declare @fieldlist varchar(max) = '';
select @fieldlist = @fieldlist + '{val' + cast([rowid] as varchar) + '} as ' + quotename([field]+'!'+cast([rowid] as varchar)+'!') + ', ' from #tmp

select
      iif(t.[rowid] = 1, 'select ', ' union all select ') + cast(t.[rowid] as varchar) +' as [Tag], ' + isnull(cast(p.[rowid] as varchar), 'NULL') +' as [Parent], ' + replace(@fieldlist, '{val' + cast(t.[rowid] as varchar)+'}', isnull(t.value, 'NULL')) as [stmt]
      into #tmp2
from #tmp t
    outer apply (select t2.rowid from #tmp t2 where t2.rid = t.parentid) p;
    
declare @sql varchar(max) = '';
with x (data) as (
    select replace(replace(replace(replace(replace(replace(left(stmt, len(stmt)-1), '{val1}', 'NULL'), '{val2}', 'NULL'), '{val3}', 'NULL'), '{val4}', 'NULL'), '{val5}', 'NULL'), '{val6}', 'NULL')
    from #tmp2 for xml path(''), type
) 
select @sql = data.value('.', 'varchar(max)')
from x;

set @sql += ' for xml explicit';

exec (@sql);