I am trying to copy data from one database table (backup) to a similar table on the product database. Request:

INSERT INTO candidates SELECT * FROM [estaffdb-20171101192519].dbo.candidates where creation_date < '2017-01-01'

Error: Message 527, level 16, state 2, line 1 Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query.

The link to the table structure https://docs.google.com/spreadsheets/d/1EOyQcj7XPie6ZOokoYcQUit-NESExaJ-AhRPhD7fdHU/edit?usp=sharing

I understand correctly that all XML-type fields need to be wrapped in a CONVERT (XML, field_name) ?? Or are there other options?

PS How dangerous is it to run such a query? data almost a million.

 INSERT INTO dbo.candidates SELECT *, CONVERT( XML, idata_division_id ), CONVERT( XML, roles ), CONVERT( XML, participant_events), CONVERT( XML, participant_event_group_id), CONVERT( XML, prev_educations), CONVERT( XML, passport), CONVERT( XML, external_registrations), CONVERT( XML, doc_info), CONVERT( XML, skills), CONVERT( XML, idata_citizenship_id), CONVERT( XML, idata_location_id), CONVERT( XML, metro_station_id), CONVERT( XML, idata_source_id), CONVERT( XML, profession_id), CONVERT( XML, idata_profession_id), CONVERT( XML, prev_jobs), CONVERT( XML, spots), CONVERT( XML, attachments), CONVERT( XML, hot_events), CONVERT( XML, multi_role_id), CONVERT( XML, external_uids), CONVERT( XML, desired_division_id), CONVERT( XML, job_target), CONVERT( XML, idata_group_id), CONVERT( XML, multi_attachment_type_id), CONVERT( XML, exclusive_access) FROM [estaffdb-20171101192519].dbo.candidates WHERE creation_date < '2017-01-01'

    1 answer 1

    The data types to be inserted must match the data types of the columns in the final table. Also the number of columns must match.

    Here is an example with the same error:

     declare @xml xml select @xml = '<x>1</x>' create table #MyTest (id int, text nvarchar(8)) insert #MyTest values (1,@xml) select * from #MyTest drop table #MyTest
    • Good day! Thanks for the answer. Only I cannot understand how data types and columns can differ? I copy data to a table from the backup of this table. That is, the tables are the same. It’s just that there is data in one that is not in the other ... What follows is that the columns are also the same. - Damir Gabdullin
    • The error is precisely because of the casting, write down the matching columns, so it will be easier to find the problem - Nick Proskuryakov