I agree that you could use
\\\server\share\Whatever without needing to map a drive at all (so UNC path only) but the security context running the T-SQL will need appropriate permissions to the SHARE and the NTFS of the full path to ensure it can do whatever commands you are running.
You could use
You could also use:
IF NOT EXIST Z: NET USE Z: \\server\share
Or perhaps something like the below with plugging in your variables:
DECLARE @folder AS VARCHAR(1000) = '\\server\share\folder1\folder2\finalfolder\'
DECLARE @command VARCHAR(500) = 'DIR /B "' + @folder + '*.xml"'
DECLARE @file VARCHAR(100)
DECLARE @filesinafolder TABLE (filenameswithfolder VARCHAR(500))
DECLARE @sql NVARCHAR(4000)
-- create global temp table
IF OBJECT_ID('tempdb..##globalname') IS NOT NULL
DROP TABLE ##globalname
CREATE TABLE ##globalname (
INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command