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?

Jonas Lincoln · Accepted Answer · 2010-01-08 13:16:37Z

4

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.

answered Jan 8, 2010 at 13:16

Jonas Lincoln

9,8079 gold badges38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Checking for empty xml column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related