DECLARE @sql VARCHAR(1000);
DECLARE @sqlString varchar(1000);
DECLARE @Directory VARCHAR(100);
DECLARE @FileName VARCHAR(30);
DECLARE @DirAndFile VARCHAR(130);
DECLARE @rowcount INT;
DECLARE @ErrMsg VARCHAR(300);

SET @Directory = '';
SET @FileName = '';
SET @DirAndFile = '';
-- Very important to include db name here: FROM [ilovebooks].[dbo].[XmlData]!!!
-- otherwise you get an error that is not raised.
SET @sqlString = 'SELECT XmlVariable FROM [ilovebooks].[dbo].[XmlData]';
SELECT @Directory = [Xmlout] FROM [dbo].[00FileLocations];
SELECT @FileName = XmlFileName FROM XmlData;
SET @DirAndFile = @Directory + '\' + @FileName;

-- If the xml file exists, copy the data.
DECLARE @fileexists BIT;
SET @fileexists = dbo.udf_FileExists(@DirAndFile);
IF @fileexists = 1
	BEGIN
		SET @sql = 'bcp "' + @sqlString + '" '
				+ 'queryout "' + @DirAndFile + '" -c -T -S'
				+ @@servername;
		PRINT @sql;
		EXEC master..xp_cmdshell @sql
		SET @rowcount = @@ROWCOUNT;
		IF @rowcount <> 1
			BEGIN
				SET @ErrMsg = 'Error. Rowcount not equal to one on copying xml to file in ' + @Thisprocname
				RAISERROR(@ErrMsg, 16, 1)
			END
	END
	ELSE
		BEGIN
			--DECLARE @ErrMsg VARCHAR(300);
			SET @ErrMsg = 'Error. In SProc ' + @Thisprocname + '. File not found!'
			RAISERROR(@ErrMsg, 16, 1)
		END