How to map a drive in a SQL Server script
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 PUSHD and POPD as:

PUSHD 
\\server\share 
Command \subfolder1\*.txt 
POPD


You could also use: 

IF NOT EXIST Z: NET USE Z: \\server\share
command z:\folder\files.txt


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 (
StepResultID VARCHAR(50)
,StartTime VARCHAR(50)
,StopTime VARCHAR(50)
,ReturnCode INT
)

INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command