Skip to content
Advertisement

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?

Suppose the xp_dirtree output is:

What I want is:

Advertisement

Answer

If I understand you correct you want something like this:

Test data:

Updated the parent id

Query

Result

EDIT

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

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement