get full path from sys.xp_dirtree

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

get full path from sys.xp_dirtree

Author

Topic

lilinikco
Starting Member

28 Posts

Posted - 2014-12-08 : 04:42:27

hello all.
I have this script:
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit
, ParentDirectory int
,flag tinyint default(0));

INSERT #DirectoryTree (subdirectory,depth,isfile)

EXEC master.sys.xp_dirtree 'E:\ElectronicArchieve',0,1;

UPDATE #DirectoryTree
SET ParentDirectory = (
SELECT MAX(Id) FROM #DirectoryTree
WHERE Depth = d.Depth - 1 AND Id < d.Id
)
FROM #DirectoryTree d;

WITH CTE as (
SELECT
Id, CAST(SubDirectory as nvarchar(255)) as SubDirectory,
Depth, ParentDirectory, CAST('' as nvarchar(255)) as Parent,isfile,flag
FROM #DirectoryTree WHERE ParentDirectory IS NULL
UNION ALL
SELECT
d.Id, CAST(d.SubDirectory as nvarchar(255)),
d.Depth, d.ParentDirectory, CAST(CTE.SubDirectory as nvarchar(255)),d.isfile,d.flag
FROM #DirectoryTree d
INNER JOIN CTE ON d.ParentDirectory = CTE.Id
)
SELECT * FROM CTE order by id

now I want to have file's full path, I mean I want to have below Result:
id subdirectory depth parentDirectory parent isfile flag fullpath
1 a 1 null 0 0 E:\ElectronicArchieve\a
2 b 2 1 a 0 0 E:\ElectronicArchieve\a\b
3 c.jpg 3 2 b 0 0 E:\ElectronicArchieve\a\b\c.jpg
How can I have this Result?please correct my code.thanks

lili@@

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources