title: Changing hard coded path to variable in MS SQL
body: <p>Im using the following code and all works correctly</p>
  
  <pre><code>DECLARE @Directory varchar(100)
  SELECT @Directory = 'c:\XML\'
  
  DECLARE @FileExist int
  DECLARE @FileName varchar(500),@DeleteCommand varchar(1000),@FullFileName varchar(500), @SQLFullFileName varchar(500)
  
  
  DECLARE @X XML
  SELECT @X = CONVERT(xml,[ICECAT-interface],2)
  FROM OPENROWSET(BULK 'C:\XML\1382.xml',SINGLE_BLOB) AS Import([ICECAT-interface])
  
  select P1.X.value('@ID', 'int') as ProductID,
         P2.X.value('@ID', 'int') as ProductID
  
  
   from @X.nodes('/ICECAT-interface/Product') as P1(X)
   cross apply P1.X.nodes('ProductRelated') as PR(X)
   cross apply PR.X.nodes('Product') as P2(X)
  </code></pre>
  
  <p>if i replace the line with the filename in C:\XML\1382.xml with this  line</p>
  
  <pre><code> SELECT @X = CONVERT(xml,[ICECAT-interface],2) 
   FROM OPENROWSET(BULK ' + @FullFileName + ' ,SINGLE_BLOB) AS Import([ICECAT-interface])
  </code></pre>
  
  <p>it errors saying the file doesn't exist, but in debug mode i can see the variable @FullFileName exists and is correct.</p>
  
  <p>Any input would be appreciated.</p>
  
  <p>Thanks</p>
  
  <p>John</p>