The data types xml and varchar are incompatible in the equal to operator.

Question

Post reply

The data types xml and varchar are incompatible in the equal to operator.

anupamabr

SSC Eights!

Points: 930
More actions
October 8, 2008 at 11:42 am

#191908

Thanks in advance
i am trying to update a table which contains xml type data field
my query is as follows
Update ProductBase
set CrossSell.modify('
')
where CrossSell = 'NULL'
i dont have any idea about what should be the value inside set CrossSell.modify( )
i am trying to update the ProductBase table which contains CrossSell dtafield (xml type) with an inserted value which gets from clr trigger using xmlwriter
please help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135381 More actions · Answer 1

do you mean

update yourtable

set yourcolumn = ''

where yourcolumn = 'NULL' -- column actually contains value NULL

or

update yourtable

set yourcolumn = ''

where yourcolumn is NULL -- column actually is NULL i.e. unknown value

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

anupamabr SSC Eights! Points: 930 More actions · Answer 2

in my ProductBase table the CrossSell field contains null value

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Continuing the cross-posted thread from here: http://www.sqlservercentral.com/Forums/Topic582839-149-1.aspx#bm582875

anupamabr,

If when you select the XML value, it returns the word NULL, it means that there is no value. If you inserted the value 'NULL' into an XML datatype and then selected it, you'd get a blank value returned.

So, what this means is that you need to change you comparison from = 'NULL' to IS NULL.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

anupamabr SSC Eights! Points: 930 More actions · Answer 4

still iam getting the same error

thanks for your quick response

John Rowan SSC Guru Points: 56440 More actions · Answer 5

If it is NULL, why not just set it to the new XML value without going through the XML DML?

DECLARE @ProductBase TABLE (ID int IDENTITY(1,1), CrossSell xml)

INSERT INTO @ProductBase

DEFAULT VALUES

SELECT * FROM @ProductBase

Update @ProductBase

set CrossSell = ' '

where CrossSell IS NULL

SELECT * FROM @ProductBase

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

anupamabr SSC Eights! Points: 930 More actions · Answer 6

Thanks alot Jhon...it is working nw.....

thanks again