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