title: T-SQL, Load XML data into a local variable
body: <p>I would like to know, how can I load the XML content from an arbitrary file into a local variable?</p>
  
  <p>This works for a fixed file:</p>
  
  <pre><code>DECLARE @xml XML
  SET @xml = 
  (
    SELECT *
    FROM OPENROWSET(BULK 'C:\data.xml', SINGLE_BLOB) AS data
  )
  </code></pre>
  
  <p>However, I would like to load the data from any arbitrary file.</p>
  
  <p>This does not work (as BULK seems to only support String arguments)</p>
  
  <pre><code>DECLARE @file NVARCHAR(MAX) = 'C:\data.xml'
  DECLARE @xml XML
  SET @xml = 
  (
    SELECT *
    FROM OPENROWSET(BULK @file, SINGLE_BLOB) AS data
  )
  </code></pre>
  
  <p>I've also tried the following (without success, as the local variable (@xml) seems to be out of scope when the EXEC is performed):</p>
  
  <pre><code>DECLARE @file NVARCHAR(MAX) = 'C:\data.xml'
  DECLARE @xml XML
  DECLARE @bulk NVARCHAR(MAX) = 'SET @xml = (SELECT * FROM OPENROWSET(BULK ''' + @file + ''', SINGLE_BLOB) AS data)'
  EXEC (@bulk)
  </code></pre>
  
  <p>I'm guessing I need to use a temporary table, but how?</p>