Checking for empty xml column

Question

Next query runs successfully:

select top(100) * from PackageSessionNodes 
where Cast(ContentInteractions as nvarchar) != ''

Next gives me error Target string size is too small to represent the XML instance

update PackageSessionNodes set ContentInteractions = '<contentinteractions />'
where Cast(ContentInteractions as nvarchar) = ''

ContentInteractions is an xml column. No DDT defined on it.

How can I solve the second query so that I get those records with '' as xml?

I seems like SqlServer cannot deal with empty values that are stores in a xml column... but you can insert them... how's that?

Answer 1

The problem is with your CAST. When you don't specify the length of nvarchar, it defaults to 30 characters in the CAST-function. So what what your statement really says is

update PackageSessionNodes set ContentInteractions = '<contentinteractions />'
where Cast(ContentInteractions as nvarchar(30)) = ''

So if the XML-content of ContentInteractions is serialized to more than 30 characters, you get this problem.