How to map a drive in a SQL Server script I am trying to execute below script to Network a Drive

use master
go
exec sp_configure 'show advanced options', 1;
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1;
go
reconfigure
go

EXEC XP_CMDSHELL 'net use Z: \\RemoteServerName\SharedName'
EXEC XP_CMDSHELL 'Dir Z:'


Is there any script that  I can use to connect the network drive in long term
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