7

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('')?

  • String concatenation like this won't ever be pretty. Can't you do it in your app? – JNK Apr 17 '14 at 20:18
  • Unfortunately no. This is going to be part of a stored procedure for a report, and I have very limited control over what that report does. It works well but the ONLY issue is the escaping of special characters. @JNK – Kehlan Krumme Apr 17 '14 at 20:18
14

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.