question

andypullar avatar image
andypullar asked

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
xmlstringbinarytruncated
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

andypullar avatar image andypullar commented ·
Thanks for the reply. All sorted now. Yes you were correct, I was looking at the wrong field. The source had changed to nvarchar(max) and the destination was xml. Because this was the only field that changed I assumed (incorrectly) that it was the culprit when in fact it was another field. I changed that field and it's all good now. Cheers.
0 Likes 0 ·
gm_ignat avatar image
gm_ignat answered
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)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.