Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /dir1
- /file1
- /dir2
- /dir2/dir3
- /dir2/dir3/file2
- /*
- Setup:
- - Create the table to hold the files
- - nodeDepth is identifier of the depth for readability
- - fullpath is the full path of the file or directory
- - nodePath is the HierarchyID
- - nodePath identifies the row within the tree
- */
- DECLARE @t TABLE (
- nodeID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
- nodeDepth VARCHAR(10) NOT NULL,
- fullPath VARCHAR(20) NOT NULL,
- nodePath HIERARCHYID NOT NULL
- )
- /*
- Load the nodePath value with the Parse command:
- - The root node has a single /
- - Every nodePath must begin and end with /
- - /1/2/ the second item on level 2
- */
- INSERT @t (fullPath, nodeDepth, nodePath) VALUES
- ('/','1',HIERARCHYID::Parse('/')),
- ('/dir1','1.1',HIERARCHYID::Parse('/1/1/')),
- ('/file1','1.2',HIERARCHYID::Parse('/1/2/')),
- ('/dir2','1.3',HIERARCHYID::Parse('/1/3/')),
- ('/dir2/dir3','1.3.1',HIERARCHYID::Parse('/1/3/1/')),
- ('/dir2/dir3/file2','1.3.1.1',HIERARCHYID::Parse('/1/3/1/1/'))
- SELECT *
- FROM @t
- nodeID nodeDepth fullPath nodePath
- ----------- ---------- -------------------- --------
- 1 1 / 0x
- 2 1.1 /dir1 0x5AC0
- 3 1.2 /file1 0x5B40
- 4 1.3 /dir2 0x5BC0
- 5 1.3.1 /dir2/dir3 0x5BD6
- 6 1.3.1.1 /dir2/dir3/file2 0x5BD6B0
- SELECT *
- FROM @t
- WHERE nodePath =
- (SELECT nodePath.GetAncestor(1)
- FROM @t
- WHERE fullPath = '/dir2/dir3/file2')
- nodeID nodeDepth fullPath nodePath
- ----------- ---------- -------------------- ---------
- 5 1.3.1 /dir2/dir3 0x5BD6
- SELECT *
- FROM @t
- WHERE nodePath.IsDescendantOf(
- (SELECT nodePath
- FROM @t
- WHERE fullPath = '/dir2')) = 1
- AND fullPath <> '/dir2' /* Parent is considered its own descendant */
- nodeID nodeDepth fullPath nodePath
- ----------- ---------- -------------------- --------
- 5 1.3.1 /dir2/dir3 0x5BD6
- 6 1.3.1.1 /dir2/dir3/file2 0x5BD6B0
- SELECT *
- FROM @t
- WHERE nodePath = HIERARCHYID::GetRoot()
- nodeID nodeDepth fullPath nodePath
- ----------- ---------- -------------------- --------
- 1 1 / 0x
- SELECT nodePath.GetLevel() AS level
- FROM @t
- WHERE fullPath = '/dir2/dir3/file2'
- level
- ------
- 4
Add Comment
Please, Sign In to add comment