SQL Server – Avoiding Entitized Characters with FOR XML PATH

concatsql serverxml

I have this query:

SELECT DISTINCT
    f1.CourseEventKey,
    STUFF
    (
        (
            SELECT
                '; ' + Title
            FROM
            (
                SELECT DISTINCT
                    ces.CourseEventKey,
                    f.Title
                FROM
                    CourseEventSchedule ces
                INNER JOIN Facility f 
                    ON f.FacilityKey = ces.FacilityKey
                WHERE
                    ces.CourseEventKey IN 
                    (
                        SELECT CourseEventKey
                        FROM @CourseEvents
                    )
            ) f2
            WHERE
                f2.CourseEventKey = f1.CourseEventKey
            FOR XML PATH('')
        )
    , 1, 2, '')
FROM
(
    SELECT DISTINCT
        ces.CourseEventKey,
        f.Title
    FROM
        CourseEventSchedule ces
    INNER JOIN Facility f 
        ON f.FacilityKey = ces.FacilityKey
    WHERE
        ces.CourseEventKey IN 
        (
            SELECT CourseEventKey
            FROM @CourseEvents
        )
) f1;

It produces this result set:

CourseEventKey Titles
-------------- ----------------------------------
29             Test Facility 1
30             Memphis Training Room
32             Drury Inn & Suites Creve Coeur

The data is accurate, but I can't have FOR XML PATH('') because it escapes certain special characters.

To be clear, I'm using FOR XML PATH('') because it is possible for records with the same CourseEventKey to have multiple Facility titles associated with them.

How can I retain the data returned by this query without using FOR XML PATH('')?

Best Answer

Try changing you statement to match this:

FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)')

This uses the .value xml method to solve your entitization problem.

Related Question