How to use FOR XML PATH('') in a query without escaping special characters?

Question

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 &amp; 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?

score 15 · Answer 1 · edited Nov 13 '18 at 23:28

Use ,TYPE).value('.','NVARCHAR(MAX)') and your special characters will not be escaped:

SELECT DISTINCT
            f.CourseEventKey,
            (
                SELECT      f.Title + '; ' AS [text()]
                FROM        @Facilities
                WHERE       CourseEventKey = f.CourseEventKey
                ORDER BY    f.Title
                FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
            AS Facilities
FROM        @Facilities f

Credit for this goes to Rob Farley.

UPDATE:
I just heard about this new method. I haven't tested it thoroughly yet, and would appreciate any feedback. We can replace [text()] with [processing-instruction(x)], like this

select 'hello & there >' as [processing-instruction(x)] FOR XML PATH('')

will return

<?x hello & there >?>

We just need to strip off the <? ... ?>

@Struco your suggestion for commenting out the ... ?> doesn't scale: http://sqlfiddle.com/#!18/4981ac/1/0 — Baodad, Oct 02 '18 at 16:34

score 3 · Accepted Answer · edited May 23 '17 at 10:31

3

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.

edited May 23 '17 at 10:31

Community

1
1

answered Apr 17 '14 at 18:47

mwigdahl

16,268
7
50
64

You are correct, that's exactly what I'm trying to do. I'll take a look, but any additional help (especially code examples) are greatly appreciated. – keeehlan Apr 17 '14 at 18:51
1

For those just finding this, see @Baodad's below (as well as several others on SO), which provides a safe, built-in method. – harpo Feb 22 '17 at 17:03

score 1 · Answer 3 · answered Oct 03 '18 at 09:09

Adding to the latest solution provided by @Baodad, simply REPLACE the <? ... ?>.

SELECT DISTINCT
        f.CourseEventKey,
        REPLACE(
        REPLACE(
                (
                SELECT f.Title + '; ' AS [processing-instruction(x)]
                FROM Facilities
                WHERE CourseEventKey = f.CourseEventKey
                ORDER BY f.Title
                FOR XML PATH('')
                )
                , '<?x','')
                , '?>','') Facilities
FROM Facilities f`

Your output will be:

| CourseEventKey |                                 Facilities |
|----------------|--------------------------------------------|
|             29 |        Test Facility 1;  Test Facility 1;  |
|             29 |      Test Facility 33;  Test Facility 33;  |
|             30 |                    Memphis Training Room;  |
|             31 |                            Another place;  |
|             32 |  Drury Inn & Suites;  Drury Inn & Suites;  |
|             32 |    Yet Another place;  Yet Another place;  |

This works because your final output is not in XML, otherwise REPLACE would simply not work as the characters would remain encoded.

Thanks @tgolisch and RoachLord for your pointers, i was trying to avoid hijacking Baodad solution, wich helped me in my issue. — Struco, Oct 03 '18 at 09:12

score 0 · Answer 4 · edited Jun 02 '23 at 19:58

0

I had some difficulty with this as well. Here is what I found that worked for me.

Select (
    Select some_field As [text()]
    From some_table
    For Xml Path(''), Type, Root('root')
).value('(/root)[1]', 'NVarchar(Max)')

edited Jun 02 '23 at 19:58

Evgeny Lukashevich

1,500
14
26

answered Jun 01 '23 at 17:22

ItalicIntegral

35
5

How to use FOR XML PATH('') in a query without escaping special characters?

4 Answers4