Next query runs effectively:
select top(100) * from PackageSessionNodes where Cast(ContentInteractions as nvarchar) != ''
Next provides me with error Target string dimensions are they canrrrt represent the XML instance
update PackageSessionNodes set ContentInteractions = '<contentinteractions />' where Cast(ContentInteractions as nvarchar) = ''
ContentInteractions is definitely an xml column. No DDT defined onto it.
How do i solve the 2nd query to ensure that I recieve individuals records with '' as xml?
I appears like SqlServer cannot cope with empty values which are stores inside a xml column... however, you can place them... how's that?
The issue is together with your CAST. Whenever you don't specify the size of nvarchar, it defaults to 30 figures within the CAST-function. What exactly what your statement really states is
update PackageSessionNodes set ContentInteractions = '<contentinteractions />' where Cast(ContentInteractions as nvarchar(30)) = ''
Therefore if the XML-content of ContentInteractions is serialized to a lot more than 30 figures, you receive this issue.