title: Passing path of File as a Parameter in a Stored Procedure
body: <p>I have a stored procedure that uses a file path to load its data. When I give the path directly in the procedure as shown below the procedure works and the data is loaded.</p>
  
  <pre><code>CREATE PROCEDURE main.usp_importXML(@file VARCHAR)
  AS
  BEGIN
      DECLARE @xmlFile as XML
  
      SET @xmlFile = (SELECT CONVERT(XML,BulkColumn) as BulkColumn
      FROM OPENROWSET (BULK  'C:\Users\User\Desktop\people.xml', SINGLE_BLOB) AS t)
      INSERT INTO main.tempXML (name,surname,dob,gender,locality,postcode,job,salary,email)
      SELECT
       name = t.value ('name[1]', 'NVARCHAR(20)'),
       surname = t.value ('surname[1]', 'NVARCHAR(20)'),
       dob = t.value ('dob[1]', 'DATETIME'),
       gender = t.value ('gender[1]', 'CHAR'),
       locality = t.value ('locality[1]', 'NVARCHAR(20)'),
       postcode = t.value ('postcode[1]', 'NVARCHAR(20)'),
       job = t.value ('job[1]', 'NVARCHAR(50)'),
       salary = t.value ('salary[1]', 'INTEGER'),
       email = t.value ('email[1]', 'NVARCHAR(50)')
      FROM @xmlFile.nodes('/persons/person') AS xTable(t);
  END
  GO
  </code></pre>
  
  <p>However when I try to pass the path as a parameter, code shown below, this error occurs:</p>
  
  <blockquote>
    <p>Msg 4860, Level 16, State 1, Procedure usp_importXML, Line 6 [Batch Start Line 38]<br>
    Cannot bulk load. The file "+@file+" does not exist.</p>
  </blockquote>
  
  <p>Code:</p>
  
  <pre><code>CREATE PROCEDURE main.usp_importXML(@file VARCHAR)
  AS
  BEGIN
      DECLARE @xmlFile as XML
  
      SET @xmlFile = (SELECT CONVERT(XML,BulkColumn) as BulkColumn
      FROM OPENROWSET (BULK  '+@file+', SINGLE_BLOB) AS t)
      INSERT INTO main.tempXML (name, surname, dob, gender, locality, postcode, job, salary, email)
          SELECT
              name = t.value ('name[1]', 'NVARCHAR(20)'),
              surname = t.value ('surname[1]', 'NVARCHAR(20)'),
              dob = t.value ('dob[1]', 'DATETIME'),
              gender = t.value ('gender[1]', 'CHAR'),
              locality = t.value ('locality[1]', 'NVARCHAR(20)'),
              postcode = t.value ('postcode[1]', 'NVARCHAR(20)'),
              job = t.value ('job[1]', 'NVARCHAR(50)'),
              salary = t.value ('salary[1]', 'INTEGER'),
              email = t.value ('email[1]', 'NVARCHAR(50)')
          FROM 
              @xmlFile.nodes('/persons/person') AS xTable(t);
  END
  GO
  
  EXEC main.usp_importXml [C:\Users\User\Desktop\people.xml]
  </code></pre>
  
  <p>main.tempXML table structure:</p>
  
  <pre><code>CREATE TABLE main.tempXML
  (
      id       UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
      [name]   VARCHAR(100), 
      surname  VARCHAR(100),
      dob      VARCHAR(100),
      gender   VARCHAR(10),
      locality VARCHAR(100),
      postcode VARCHAR(100),
      job      VARCHAR(100),
      salary   VARCHAR(20),
      email    VARCHAR(100)
  );
  </code></pre>
  
  <p>people.xml data structure:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
    -&lt;persons&gt;
      -&lt;person&gt;
        &lt;name&gt;Jacob&lt;/name&gt;
        &lt;surname&gt;Naizer&lt;/surname&gt;
        &lt;dob&gt;19840717&lt;/dob&gt;
        &lt;gender&gt;M&lt;/gender&gt;
        &lt;locality&gt;Mellieha&lt;/locality&gt;
        &lt;postcode&gt;MEL876&lt;/postcode&gt;  
        &lt;job&gt;JAVA Developer&lt;/job&gt;
        &lt;salary&gt;20733&lt;/salary&gt;
        &lt;email&gt;Jacob.Naizer1145@gmail.com&lt;/email&gt;
    &lt;/person&gt;
   &lt;/persons&gt;
  </code></pre>