OPENROWSET: sp_executesql statement failing at @param

Question

I am developing a program to pull in the XML file that WordPress enables you to download (essentially a backup copy).

At this point I was automating the process to allow frequent backup of my data on SQL Server, and for some reason I am stuck at developing the query to run the OPENROWSET where the XML file will be located.

DECLARE @SQL NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(500) = N'@fstring NVARCHAR(MAX)'
DECLARE @string VARCHAR(MAX) = 
N'C:\[FilePath]\Reviews\thehesperian2016-07-29.xml'

SET @SQL = 
N'INSERT INTO #Temp (Extract_Date, XMLDATA)
SELECT GETDATE()
     , A.*
FROM OPENROWSET(BULK @fstring, SINGLE_BLOB, CODEPAGE = ' + '''RAW''' + ') AS A'

EXEC sp_executesql @SQL
                 , @ParamDefinition
                 , @fstring = @string

The error:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@fstring'.

I can turn this into a simple query on a table in the predicate, so I have reason to suspect it is the way the filepath is read. I've spent a few hours racking my brain trying to figure out why this is wrong. While I COULD use QUOTENAME as in this example in the BULKINSERT, I was hoping to embed all of that in the dynamic SQL (thus still use sp_executesql)

What or why am I doing this wrong? Any help will be greatly appreciated. - Regards,

ANSWER

OPENROWSET - MSDN declares in its own paragraph:

OPENROWSET does not accept variables for its arguments.

QUOTENAME is sufficient, although I did run a few minor REPLACEfunctions anyways.

Answer 1

The data file path the OPENROWSET function does not allow a parameter. Instead, build the needed string with the literal:

DECLARE @string varchar(MAX) = N'C:\[FilePath]\Reviews\thehesperian2016-07-29.xml';
DECLARE @SQL nvarchar(MAX);
SET @SQL = 
N'INSERT INTO #Temp (Extract_Date, XMLDATA)
SELECT GETDATE()
     , A.*
FROM OPENROWSET(BULK ' + QUOTENAME(@string, '''') + ', SINGLE_BLOB, CODEPAGE = ''RAW'') AS A';
EXEC sp_execute @SQL;

--EXECUTE(@SQL);

UPDATE:

Added QUOTENAME in case the provided file path is from an untrusted source. Also, note that OPENROWSET queries are not autoparameterized. It makes no difference whether one executes the query with sp_executesql or EXECUTE here.