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

Hello,

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

AS

(

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

FROM Channel

)

SELECT

cr.p_c_code,

cr.orderid,

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

INNER JOIN CTE_Channel c

ON cr.campaign_code = c.campaign_code

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

WHERE

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

Vinnie

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