It does only as its name implies … tests for the existence of a FILE.
It returns 0 if you try to use it to test for a folder
declare @Path varchar(128)
select @Path = 'C:\'
create table #a(FileExists bit, FileIsDirectory bit, ParentDirectoryExists bit)
insert #a exec master..xp_fileexist @Path
select * from #a
drop table #a
DIR \B
declare @Path varchar(128) ,@FileName varchar(128)
select @Path = 'C:\temp\jon'
declare @cmd varchar(1000)
create table #a(s varchar(1000))
select @cmd = 'dir /B ' + @Path
insert #a exec master..xp_cmdshell @cmd