How to use FOR XML PATH('') in a query without escaping special characters?
escaping, for-xml-path, sql, sql-server, t-sql
Solution
I think you're going to have to manually wrap the Facilities inline query block with REPLACE statements to reverse the automatic escaping.
It sounds like what you're wanting to do is concatenate multiple facilities that could present a given course. Have you considered other options? This question has several possible approaches that don't have an issue with escaping your characters.
Problem
I have this query: ``` SELECT DISTINCT f.CourseEventKey, ( SELECT f.Title + '; ' AS [text()] FROM @Facilities WHERE CourseEventKey = f.CourseEventKey ORDER BY f.Title FOR XML PATH('') ) Facilities FROM @Facilities f ``` It produces this result set: ``` CourseEventKey Facilities -------------- ----------------------------------- 29 Test Facility 1; 30 Memphis Training Room; 32 Drury Inn & Suites Creve Coeur; ``` The data is fine, but the `&` is actually an encoded `&`, which is not suitable for my purposes. How can I modify this query to return the original values of the special characters in my data?