How to pass path to Bulk in OPENROWSET command

Question

I have a XML file. I would like to run a query on it. I am using OPENROWSET(BULK ... , SINGLE_BLOB) like below code.

    DECLARE @XML XML

    SELECT 
         @XML = CAST( c1 AS XML)    
    FROM OPENROWSET(BULK '\\ServerA\Test\AA.xml', SINGLE_BLOB) AS T1(c1);

    SELECT @XML

But I should use a variable for path, like below code:

    DECLARE @XML_File_Path NVARCHAR(1000) 

    SET  @XML_File_Path = '\\ServerA\Test\AA.xml'

    DECLARE @XML XML

    SELECT 
            @XML = CAST( c1 AS XML)    
    FROM OPENROWSET(BULK @XML_File_Path, SINGLE_BLOB) AS T1(c1);

    SELECT @XML

and this dose not work. How can I have a variable after BULK key word?

Answer 1

Use dynamic sql:

DECLARE @XMLFilePath NVARCHAR(1000)
DECLARE @SQL NVARCHAR(MAX)

SET  @XMLFilePath = '\\ServerA\Test\AA.xml'

SET @SQL = N'
DECLARE @XML XML
SELECT @XML = CAST( c1 AS XML) FROM OPENROWSET(BULK ''' + @XMLFilePath + ''', SINGLE_BLOB) AS T1(c1);
SELECT @XML'

EXEC(@SQL)