CODEWITHSUNDEEP
sql-serverstored-proceduresxml-parsing
gorkhali1
gorkhali1

Reputation: 21

SQL Server Procedure with Filename as a Parameter

I have a SQL Server procedure and I would like to have the file name as the parameter.

My code is:

CREATE PROCEDURE XMLTOSQLTABLE1 @NewName nvarchar(50)
AS
  TRUNCATE TABLE [dbo.Student]

  DECLARE @x XML
  SELECT @x = P
  FROM OPENROWSET (BULK @NewName, SINGLE_BLOB) AS Student(P)

  DECLARE @hdoc int

  EXEC sp_xml_preparedocument 
    @hdoc OUTPUT, 
    @x

  INSERT INTO [dbo.Student] 
  SELECT * 
  FROM OPENXML (@hdoc, '/class_list/student',2)
  WITH (
    name varchar(100), 
    grade varchar(100))

  EXEC sp_xml_removedocument @hdoc
GO

I would like to have the file name and the column names to be my parameter so I could easily input any XML file into the database by simply executing the procedure with the filename and column names as my parameters.

Upvotes: 0

Views: 1010

Answers (1)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

As far as your code is concerned, you have to pay attention to a few things for it to work.

Let's see:

  1. TRUNCATE TABLE [dbo.Student]

    I believe you are truncating table Student in default schema. So, it should be

    TRUNCATE TABLE Student

    or

    TRUNCATE TABLE dbo.Student

    Since you don't have no special chars or keywords in table name, forget the square brackets.

  2. SELECT @x = P FROM OPENROWSET (BULK @NewName, SINGLE_BLOB) AS Student(P)

    You can't parameterize OPENROWSET BULK file reading. Only way to do it is using dynamic SQL, see this.

    Another problem is assigning a varbinary BLOB to an xml variable, which won't work without type conversion, like CAST(P as xml). Just pay attention to what you are really loading because non-well-formatted XML segment will throw an error here.

  3. Another problem is using sp_xml_preparedocument procedure. Do you really need it? Using this kind of XML parsing can limit your xml resources. Also, if you forget to call (or skip because of an error) sp_xml_removedocument you will get errors because of this. AFAIK, server restart will help here.

    Note: sp_xml_preparedocument is vulnerable, so pay attention where your XML files come from.

When you clear this out, a few question stay open: what kind of a second parameter (column names) do you need and why? Should it be an XML parameter? Should it use for a hint what kind of children should be extracted from bulk loaded xml root?

When you sort this out, I believe that a stored procedure could be constructed, but not without some dynamic expression.

Upvotes: 1

Related Questions