SQL Server Filetable – How to Get Path Locator for Subfolder by Literal Path

filestreamfiletablesql serversql-server-2012

I have a filetable which have several subtables.

For example

FileTable
->subtable1
->->subtable1.1
->->subtable1.2
->subtable2
->subtable3

etc

How to get SQL Server path_locator for subtable given by something like '/subtable1/subtable1.1' ?

I need it to move files from root to subfolder. I tried this, but its does not work

declare @parent hierarchyid;
set @parent = GetPathLocator('folder2/folder2.1')

update physical_files set path_locator = @parent.GetDescendant(NULL, NULL)
where stream_id = '494D5C8B-AC22-E411-A464-00259060BBB9';

SQL Server 2012 reports:

Msg 33423, level 16, state 1
Invalid FileTable path name or format.

Best Answer

I found a solution. Here is example where filetable name is 'physical_files', target path is 'folder1', target file have stream_id = 'FF9CF452-B522-E411-A464-00259060BBB9'

declare @path varchar(MAX);
set @path = '\folder';

declare @parent hierarchyid;
set @parent = GetPathLocator(CONCAT(FileTableRootPath('dbo.physical_files'), @path));
select @parent

update physical_files set path_locator = @parent.GetDescendant(NULL, NULL)
where stream_id = 'FF9CF452-B522-E411-A464-00259060BBB9';
Related Question