SQL - OPENROWSET with variable instead of string path

Question

I have a query that runs without a variable. It works as expected:

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\xml\hamlet.xml', SINGLE_BLOB) AS x;

However when I add in a variable. It doesn't work (I replaced a string with a variable). I get this error => Incorrect syntax near '@path'.

DECLARE @path varchar(50) = 'C:\xml\hamlet.xml';

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK @path, SINGLE_BLOB) AS x;

Does anyone know what is wrong here?

Answer 1

The parametrized dynamic SQL query may not work. Try concatenating the path explicitly:

DECLARE @path varchar(50) = 'C:\xml\hamlet.xml', 
        @sql nvarchar(max)= ''

set @sql = '
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK ''' + @path +''', SINGLE_BLOB) AS x;'

exec sp_executesql @sql,N''