Use SQL Server recursive common table expression to get full path of all files in a folder(with subfolders)

There is a SQL Server undocumented extended stored procedure called xp_dirtree, which can return all files and folders name (include subfolders) in a table format. To practice my understanding of recursive CTE, I decide to use it to get the full path of all files in a specified folder(include subfolders). However, after an hour of head scratch I still can’t figure out the correct way to do it. The following code is what I have currently. Can this purpose be implemented with recursive CTE?

DECLARE @dir NVARCHAR(260) ;
SELECT  @dir = N'c:temp' ;

IF RIGHT(@dir, 1) <> '' 
    SELECT  @dir = @dir + '' ;

IF OBJECT_ID('tempdb..#dirtree', 'U') IS NOT NULL 
    DROP TABLE #dirtree ;
CREATE TABLE #dirtree
(
 id INT PRIMARY KEY
        IDENTITY,
 subdirectory NVARCHAR(260),
 depth INT,
 is_file BIT
) ;

INSERT  INTO #dirtree
        EXEC xp_dirtree 
            @dir,
            0,
            1 ;

SELECT  *
FROM    #dirtree ;

WITH    files
          AS (
              SELECT    id,
                        subdirectory,
                        depth,
                        is_file, subdirectory AS path
              FROM      #dirtree
              WHERE     is_file = 1
                        AND depth <> 1
   UNION ALL
               -- ...
             )
    SELECT  *
    FROM    files ;

Suppose the xp_dirtree output is:

/*
id  subdirectory   depth   is_file
--- -------------- ------- -------
1   abc.mdf        1       1
2   a              1       0
3   a.txt          2       1
4   b.txt          2       1
5   a.rb           1       1
6   aaa.flv        1       1
*/

What I want is:

/*
path
------------------
c:tempabc.mdf
c:tempaa.txt
c:tempab.txt
c:tempa.rb
c:tempaaa.flv
*/

Answer

If I understand you correct you want something like this:

Test data:

CREATE TABLE #dirtree
(
    id INT,
    subdirectory NVARCHAR(260),
    depth INT ,
    is_file BIT,
    parentId INT
)

INSERT INTO #dirtree(id,subdirectory,depth,is_file)
VALUES
    (1,'abc.mdf',1,1),(2,'a',1,0),(3,'a.txt',2,1),
    (4,'b.txt',2,1),(5,'a.rb',1,1),(6,'aaa.flv',1,1)

Updated the parent id

UPDATE #dirtree
SET ParentId = (SELECT MAX(Id) FROM #dirtree
      WHERE Depth = T1.Depth - 1 AND Id < T1.Id)
FROM #dirtree T1

Query

;WITH CTE
AS
(
    SELECT
        t.id,
        t.subdirectory,
        t.depth,
        t.is_file
    FROM
        #dirtree AS t
    WHERE
        is_file=0
    UNION ALL
    SELECT
        t.id,
        CAST(CTE.subdirectory+''+t.subdirectory AS NVARCHAR(260)),
        t.depth,
        t.is_file
    FROM
        #dirtree AS t
        JOIN CTE
            ON CTE.id=t.parentId
    )
SELECT
    'c:temp'+CTE.subdirectory AS [path]
FROM
    CTE
WHERE
    CTE.is_file=1
UNION ALL
SELECT
    'c:temp'+t.subdirectory
FROM
    #dirtree AS t
WHERE
    is_file=1
    AND NOT EXISTS
    (
        SELECT
            NULL
        FROM
            CTE
        WHERE
            CTE.id=t.id
    )

Result

path
---------------
c:tempaa.txt
c:tempab.txt
c:tempabc.mdf
c:tempa.rb
c:tempaaa.flv

EDIT

Changed the tables used in the example to more look like the ones in your question