sql server FileTable: Create Path

 
DECLARE @Val as nvarchar(4000)
DECLARE @NextVal as nvarchar(4000)
DECLARE @Pathlocator as hierarchyid = NULL
DECLARE @ParentLocator as hierarchyid = NULL
DECLARE @LastChildLocator as hierarchyid =NULL
DECLARE curInserted CURSOR LOCAL FOR  (select * from STRING_SPLIT('a1\b2\c1.txt','\'))
OPEN curInserted
FETCH NEXT FROM curInserted INTO @NextVal
SET @Val=@NextVal
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM curInserted INTO @NextVal

--check if Directory named Val already there (in Dir with parentLocator)
if (@ParentLocator is null)
BEGIN --------- root level => need parent_Path_locator is null
SET @Pathlocator = (select path_locator from MyFile where name like @Val and  parent_Path_locator is null ) -- from main Dir
SET @LastChildLocator = (select top 1 path_locator from MyFile where parent_Path_locator is null order by path_locator desc) -- get latest child
SET @ParentLocator = hierarchyid::GetRoot()
END
ELSE ----- not root level - query with parent_Path_locator = @ParentLocator
BEGIN
SET @Pathlocator = (select path_locator from MyFile where name like @Val and  parent_Path_locator = @ParentLocator ) -- from subdir
SET @LastChildLocator = (select top 1 path_locator from MyFile where parent_Path_locator=@ParentLocator order by path_locator desc) -- get latest child
END

if (@Pathlocator is null) --doesn't exist => have to create
BEGIN
-- get new PathLocator in parent Folder
SET @Pathlocator = @ParentLocator.GetDescendant(@LastChildLocator,NULL)
if (@@FETCH_STATUS = 0) -- not the last entry => its a dir
BEGIN
PRINT 'Create SubDir:' + @Val
INSERT INTO [dbo].MyFile ([name],is_directory,path_locator) SELECT @Val, 1, @Pathlocator  -- insert next child
END
ELSE --last entry =>  file
BEGIN
PRINT 'Create File:' + @Val
INSERT INTO [dbo].MyFile ([name],[file_stream],path_locator) SELECT @Val, CONVERT(VARBINARY(MAX),'TestFileText'), @Pathlocator  -- insert next child
END
END
ELSE
BEGIN
PRINT @Val +' already exists ...'
END
SET @Val=@NextVal
SET @ParentLocator = @Pathlocator
END --WHILE
CLOSE curInserted


select file_stream.GetFileNamespacePath() as RelativePath,  * from MyFile order by creation_time desc
select * from vMyFile where file_stream is null