0

I need to read the contents of a XML file using SQL commands. I use xp_cmdshell to read the file and insert all contents into temporary table.

this is my stored procedure:

ALTER PROCEDURE ReadingXMLTest (@FileName VARCHAR(255))
AS
BEGIN
    DECLARE @ExecCmd VARCHAR(255)
    SET @FileName = 'C:\PrivateData\EmployeesInfo.xml';
    CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
    SET @ExecCmd= 'type' + @FileName;
    INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd;
END

But I get this error-

The filename, directory name, or volume label syntax is incorrect.

I am sure that the specified file exists in the directory of the machine where SQL Server is running. File name, directory name, volume label everything is correct. But why?

Please help me. I tried for many times.

Aung Kaung Hein
  • 1,516
  • 5
  • 24
  • 40
sandy
  • 11
  • 2

1 Answers1

3

I replaced the file path and tested your query. It works perfectly fine.

This is the line giving you problem.

SET @ExecCmd= 'type ' + @FileName;

You need a space after type.

This is a complete dos command you have to run 'type C:\PrivateData\EmployeesInfo.xml'

Aung Kaung Hein
  • 1,516
  • 5
  • 24
  • 40