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