CODEWITHSUNDEEP
sql-server-2014sqlxml
K.Z
K.Z

Reputation: 5075

Insert data from XML file into existing SQL Server table

I need to insert data from XML file into existing SQL SERVER table. I have modified following code to INSERT INTO but its not working. I don't want to delete or create new table in this statement

DECLARE @x xml
SELECT @x=P
FROM OPENROWSET (BULK 'D:\Course Instance Marketing Data From Drupal\instance_marketing.xml', SINGLE_BLOB ) AS COURSE_INSTANCE(P)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x


SELECT *
INTO RCMI_MARKETING_SYNC
FROM OPENXML(@hdoc,'/response/item', 2)
 WITH(
 CourseInstanceKey int '@key',
idRCMI_MARKETING_SYNC int '@key' ,
title varchar(3000),
course_code varchar(3000),
market_area varchar(3000),
ssa varchar(3000),
school_owning varchar(3000),
overview varchar(3000), 
entry_requirements varchar(3000),
teaching_methods varchar(3000),
modules_and_assessment varchar(3000),
career_options_progres varchar(3000),
equipment_needed_costs varchar(3000),
work_placement_field_trips varchar(3000)
)   

  EXEC sp_xml_removedocument @hdoc

Error

Msg 2714, Level 16, State 6, Line 10
There is already an object named 'RCMI_MARKETING_SYNC' in the database.

I don't want to delete existing table

Upvotes: 2

Views: 783

Answers (2)

K.Z
K.Z

Reputation: 5075

Found answer

DECLARE @x xml
SELECT @x=P
FROM OPENROWSET (BULK 'D:\Course Instance Marketing Data From Drupal\instance_marketing.xml', SINGLE_BLOB ) AS COURSE_INSTANCE(P)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

INSERT INTO RCMI_MARKETING_SYNC
SELECT *
FROM OPENXML(@hdoc,'/response/item', 2)
WITH(
CourseInstanceKey int '@key',
idRCMI_MARKETING_SYNC int '@key' ,
title varchar(3000),
course_code varchar(3000),
market_area varchar(3000),
ssa varchar(3000),
school_owning varchar(3000),
overview varchar(3000), 
entry_requirements varchar(3000),
teaching_methods varchar(3000),
modules_and_assessment varchar(3000),
career_options_progres varchar(3000),
equipment_needed_costs varchar(3000),
work_placement_field_trips varchar(3000)
)   

  EXEC sp_xml_removedocument @hdoc

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

First of all:

Your approach with FROM OPENXML with the corresponding SPs to prepare and remove the document is outdated. You should use the appropriate XML methods

If you need help with this, please start a new question with a (reduced) example of your XML and place a link here.

Your question is not related to xml actually...

Your statement with SELECT .. INTO tbl will create this table physically. If this table exists already, you'll get an error.

Inserts into an existing table must be written slightly differently:

INSERT INTO RCMI_MARKETING_SYNC(col1, col2, col3,...) --place the actual column names here
SELECT col1, col2, col3, ... FROM SomeWhere           --replace this by your XML-shredding query.

Upvotes: 3

Related Questions