Create an Empty xml File

Here we need to simply create an empty xml file. The file will be date and time stamped in its name. The directory location will always be the same for this program. File names and data will constantly be changing.

-- Below, in these comments,  is a line of code that creates a new empty file.
-- EXEC master..xp_cmdshell 'type NUL > c:\newdir\1.txt';  -- create a new file 
-- It is easy. 
-- However, if that file already exists, the old one will be destroyed in favour of the
-- new empty file, resulting in a loss of any data in the existing file.


-- use current system date and time to build a file name
-- sysname is similar to nvarchar(128) NOT NULL
DECLARE @cmd sysname,  @newfile sysname, @newdir sysname;  
DECLARE @dtString VARCHAR(14); -- yyyymmddhhmmss
DECLARE @datetime DATETIME2;  -- DATETIME2 uses 24-hour clock for hours
DECLARE @year nvarchar(4), @month nvarchar(2), @day nvarchar(2);
DECLARE @hour nvarchar(2), @minute nvarchar(2), @second nvarchar(2);
SET @datetime = GETDATE();
SET @year = DATEPART(yyyy,@datetime);
SET @month = RIGHT('0' + CAST(DATEPART(mm,@datetime) AS varchar(2)),2);
SET @day = RIGHT('0' + CAST(DATEPART(dd,@datetime)AS varchar(2)),2);
SET @hour = RIGHT('0' + CAST(DATEPART(hh,@datetime)AS varchar(2)),2);
SET @minute = RIGHT('0' + CAST(DATEPART(mi,@datetime)AS varchar(2)),2);
SET @second = RIGHT('0' + CAST(DATEPART(ss,@datetime)AS varchar(2)),2);
SET @dtString = @year + @month + @day + @hour + @minute + @second
PRINT @dtString;
-- build the string with year, month, day, hour, minute and second
SET @newdir =  'C:\Data\ilovebooks\outgoing\'
SET @newfile = 'outfile' + @dtstring + '.xml'
-- if the file does not already exist, create it, otherwise 
-- raise an error!
DECLARE @fileexists BIT;
DECLARE @ErrMsg VARCHAR(200);
SET @fileexists = dbo.udf_FileExists(@newdir + @newfile);
IF @fileexists = 1
	BEGIN
		SET @ErrMsg = 'Error. File ' + @newdir + @newfile + ' already exists.';
		RAISERROR (@ErrMsg, 16, 1)
	END
SET @cmd = 'type NUL > ' + @newdir + @newfile
EXEC master..xp_cmdshell @cmd;  -- create a new file