SQL Server 2017

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

CREATE TABLE #folders (
[ID] [int] NOT NULL,
[FolderName] varchar(30) NULL,
[ParentID] [int] NULL
);

CREATE TABLE #files (
[ID] [int] NOT NULL,
[FileName] varchar(30) NULL,
[FolderID] [int] NULL
);

insert into #folders values
(1,'Root',0),(2,'Appl',1),(3,'Data',1),(4,'Simulator',2),(5,'V1',4),(6,'V2',4),(7,'V3',4),(8,'Backup',5),
(9,'Backup',6),(10,'Backup',7),(11,'Temp',8),(12,'Install',11),(13,'Lab',3),(14,'Plant',3),(15,'Device1',13),(16,'Device2',13),
(17,'Tags',16),(18,'Zone1',14),(19,'Zone2',14),(20,'Zone3',14),(21,'PSep',18),(22,'TSep',18),(23,'VRU',18),(24,'PSep',19),(25,'VRU',19),(26,'Tank',19);

insert into #files values
(1,'File1',6),(2,'File2',8),(3,'File3',8),(4,'File4',11),(5,'File5',15),
(6,'File6',15),(7,'File7',21),(8,'File8',22),(9,'File9',23),(10,'File10',24);

;with myCTE AS
(SELECT id, cast(folderName as varchar(4000)) as folderName, ParentID, cast('\'+ folderName as varchar(4000)) as fullPath
 FROM #folders as f
 WHERE ParentID = 0
 
 UNION ALL 
 
 SELECT f.id, cast(f.folderName as varchar(4000)), f.ParentID, cast(fullPath + '\' + f.folderName  as varchar(4000))
 FROM myCte as C
    iNNER JOIN #folders as f
    ON C.id = f.ParentID
    --LEFT JOIN #files as s
    --ON c.id  = s.folderID
)

select c.fullPath + '\' + f.fileName as fullPath
from myCTE as c
   INNER JOIN #files as f
   ON c.id = f.folderid

drop table #folders
drop table #files

fullPath
\Root\Appl\Simulator\V2\File1
\Root\Appl\Simulator\V1\Backup\File2
\Root\Appl\Simulator\V1\Backup\File3
\Root\Appl\Simulator\V1\Backup\Temp\File4
\Root\Data\Lab\Device1\File5
\Root\Data\Lab\Device1\File6
\Root\Data\Plant\Zone1\PSep\File7
\Root\Data\Plant\Zone1\TSep\File8
\Root\Data\Plant\Zone1\VRU\File9
\Root\Data\Plant\Zone2\PSep\File10