CODEWITHSUNDEEP
sqlsql-serverdatabase
Lieven Cardoen
Lieven Cardoen

Reputation: 25949

Checking for empty xml column

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?

Upvotes: 1

Views: 2912

Answers (1)

Jonas Lincoln
Jonas Lincoln

Reputation: 9757

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.

Upvotes: 4

Related Questions