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


Apologies but I have little knowledge about XML. I put together a query that was working successfully until I introduced an additional filter in the WHERE clause. Here is the code:

declare @data varchar(max) =


;WITH CTE_Channel



SELECT CAST(comment as xml) as [Web Sales Category], campaign_code

FROM Channel





CONVERT(VARCHAR, cr.order_date, 103) AS Order_Date,

REPLACE(CAST(c.query('data(val1)') as varchar(max)),' ', 'BLANK') as [Web Sales Category]

FROM Reason r

INNER JOIN contactreason cr

ON r.reason_code = cr.reason_code


ON cr.campaign_code = c.campaign_code

CROSS APPLY [Web Sales Category].nodes('xmlPaneData/field[1]') x(c)


cr.order_date BETWEEN '01/01/2020' AND '09/01/2020'

AND r.description LIKE 'Web Sales'

--AND [Web Sales Category] = 'Renewal'

--AND CAST( c.[Web Sales Category] AS varchar(max)) = 'Renewal'

ORDER BY cr.order_date

I was looking on the Internet trying to find a solution to convert the data types. My most recent attempt was to try casting as varchar(max) in the Where clause. Although the query runs it did not return any results, I know the the category is my data set.

Can anybody help explain what I need to do to make the Web Sales Category filter work correctly in the where clause?

Many Thanks


Please post sample xml data

Here is some xml from the relevant column in the table

Sorry try again

Make sure you put 3 of the following tick ` before and after the xml

Go back to all of your previous questions on this forum where you asked almost the same identical question

1 Like