Passing path of File as a Parameter in a Stored Procedure

Question

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.

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

However when I try to pass the path as a parameter, code shown below, this error occurs:

Msg 4860, Level 16, State 1, Procedure usp_importXML, Line 6 [Batch Start Line 38]
Cannot bulk load. The file "+@file+" does not exist.

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]

main.tempXML table structure:

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)
);

people.xml data structure:

<?xml version="1.0"?>
  -<persons>
    -<person>
      <name>Jacob</name>
      <surname>Naizer</surname>
      <dob>19840717</dob>
      <gender>M</gender>
      <locality>Mellieha</locality>
      <postcode>MEL876</postcode>  
      <job>JAVA Developer</job>
      <salary>20733</salary>
      <email>Jacob.Naizer1145@gmail.com</email>
  </person>
 </persons>

@Chanter- If you can share the xml structure with some data. Please also share the script of the tempXML table. — Rohit Kumar, May 20 '17 at 07:32
@RohitKumar edited and included the table and xml file structure — Chanter, May 20 '17 at 07:38
Why do you quote `'+@file+'` ? I would have used just `@file`. — minus, May 20 '17 at 07:45

score 4 · Accepted Answer · answered May 20 '17 at 07:47

1. Make use of dynamic query. 
2. Increase filename variable size. 

Create PROCEDURE main.usp_importXML(@file VARCHAR(1000))

AS
BEGIN

Declare @Query varchar(8000)

SET @Query ='
    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);'
   Print @Query
    exec(@Query)
END

Thank you @Rohit Kumat ! Worked absolutely fine :) ! – Chanter May 20 '17 at 07:51 — Chanter, May 20 '17 at 07:51

Passing path of File as a Parameter in a Stored Procedure

1 Answers1