Search code examples
sql-server-2014sqlxml

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


Solution

  • 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