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?

andrews · Accepted Answer · 2017-12-31 17:13:09Z

3

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''

edited Dec 31, 2017 at 17:13

answered Dec 31, 2017 at 17:07

andrews

2,1832 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jax Patočka Over a year ago

Incorrect syntax near 'C:'. I it doesnt recognizes the variable as a string in this situation

andrews Over a year ago

@JaxPatočka forgot to include single quotes. Try the updated answer. Note, I trust your original query works fine.

Collectives™ on Stack Overflow

SQL - OPENROWSET with variable instead of string path

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related