Inserting data from source datatype nvarchar(max) to destination datatype XML and get "string or binary data would be terminated"
This is the bit of SQL causing the issue. The field is MESSAGE and the source datatype is nvarchar(max). The destination table has the field datatype of XML. The error message is "string or binary data would be truncated". I have dumped the source data into Excel and used the LEN function to check the length of the field. The max length is 320,000 characters. XML is unlimited isn't it so this shouldn't be a problem? Or am I wrong here? Any ideas on how to fix this please? INSERT INTO ODS.dbo.WEBSERVICELOG (WEBSERVICELOGID, DATETIME, DIRECTION, MESSAGEID, MESSAGETYPE, MESSAGE, RESULT, ERROR, STATUSID, OriginSystemID) SELECT WEBSERVICELOGID, DATETIME, DIRECTION, MESSAGEID, MESSAGETYPE, CAST(REPLACE(CAST(MESSAGE AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML) AS MESSAGE, RESULT, ERROR, STATUSID, OriginSystemID FROM PLATOCHHODS.dbo.WEBSERVICELOG WHERE WEBSERVICELOGID > @WEBSERVICELOGID --AND MESSAGETYPE NOT LIKE '%ROHLIG%' ORDER BY WEBSERVICELOGID
The problem with that error message is it doesn't tell you which field would be truncated - let alone the row(s) that are/would be affected... If you have any other (n)varchar(non-max) fields, check that the destination field is at least the same size as the source field.
Check if the MESSAGE contains proper XML format. Maybe is a ' forget somewhere... See below example Declare @Message as nvarchar(MAX); set @Message = ' Tove Jani Reminder Don't forget me this weekend! ' select CAST(REPLACE(CAST(@Message AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML)