Tuesday, June 10, 2014

SQL query column with XML stored as VARCHAR

XML stored as VARCHAR can be queried by CASTing


--Example XML
Notification.EmailRequest...

--Query
SELECT a.Id, m.Message, a.TransmissionDtTm

FROM Message m

INNER JOIN MessageArchive a ON a.messageid=m.messageid

WHERE a.TransmissionError = 0

AND CAST(m.Properties AS xml).value('(/Props/Type)[1]', 'VARCHAR(128)') LIKE '%Email%'