Erik Darling shows how we can use FOR XML PATH on data which includes reserved XML characters:
The purpose of these queries is to show you hot to remove XML elements, and handle XML control characters like &, <, >, etc. All of these results return a single row, just to keep the examples simple.
Read on to learn more. One thing I’ve done in the past, when I know that there are specific reserved characters in use, is to run REPLACE() over the resultant data, changing < to < and so forth. But Erik shows us how to do it the best way.