T-SQL Case functionality
If Value column doesn't change, then for each row only latest else of your case is true. Syntax itself is correct, and it should work as you want. I'd first do SELECT to double check that constants in CASE correspond to what you have in the table:

SELECT DISTINCT
CASE Path
WHEN '\Folder1' THEN '0'
WHEN '\Folder2' THEN 'xyz'
WHEN '\Folder3' THEN '0'
END as val
FROM Database.[dbo].[Table]


It should return up to 3 rows ('0','xyz', and NULL). If it returns 1 row only(NULL), then you don't have records where the value of Path column equals to any of '\Folder1','\Folder2','\Folder3'. 

Note : the syntax of CASE you use compares value of Path column with constants using =. You might have wanted to use another logic, for instance LIKE. Then use different form of CASE :

SELECT DISTINCT
CASE 
WHEN Path LIKE '\Folder1%' OR Path LIKE '\Folder3%' THEN '0'
WHEN Path LIKE '\Folder2%' THEN 'xyz'    
END as val
FROM Database.[dbo].[Table]