CODEWITHSUNDEEP
sql-serverssms
7 Reeds
7 Reeds

Reputation: 2539

SQL Server "FOR XML" encoding HTML entities?

I am using SQL Server Management Studio. I have inherited a query that has a section that looks like:

...
ISNULL(
CASE
    WHEN LOWER(PersonClass.Detail) LIKE '%student%'
    THEN SUBSTRING((
            SELECT DISTINCT
                ' / '+STUDENT_TERM.DEPT_NAME
            FROM Warehouse.STUDENT_TERM STUDENT_TERM
                INNER JOIN Warehouse.TERM TERM
                    ON STUDENT_TERM.TERM_CD = TERM.TERM_CD
                    AND TERM.TERM_START_DT <= @fyEnd
                    AND ISNULL(TERM.TERM_END_DT, GETDATE()) >= @fyStart
            WHERE Persons.DWPersonId = STUDENT_TERM.DWPERSID FOR
            XML PATH('')
    ), 4, 100000)
END, '') AS StudentHome,
...

This is finding a student's "home department". There is the possibility that a student could have more than one home so the above works a bit like MySQL's group_concat.

My question is about an unintended artifact of the query. Several departments have names in the data warehouse that have embedded ampersands & in them like: 

A & B

The result of the query though is "HTML encoded" turning "A & B" into "A &amp; B".

If I run the inner query the result is as expected with a simple ampersand and not the encoded form. I am guessing that the FOR XML is doing the encoding.

Is there a way to do the group_concat without having the result encoded?

Upvotes: 0

Views: 253

Answers (1)

PeterHe
PeterHe

Reputation: 2766

You can get the value from the xml instead of cast to string:

ISNULL(
CASE
    WHEN LOWER(PersonClass.Detail) LIKE '%student%'
    THEN SUBSTRING((
            SELECT DISTINCT
                ' / '+STUDENT_TERM.DEPT_NAME
            FROM Warehouse.STUDENT_TERM STUDENT_TERM
                INNER JOIN Warehouse.TERM TERM
                    ON STUDENT_TERM.TERM_CD = TERM.TERM_CD
                    AND TERM.TERM_START_DT <= @fyEnd
                    AND ISNULL(TERM.TERM_END_DT, GETDATE()) >= @fyStart
            WHERE Persons.DWPersonId = STUDENT_TERM.DWPERSID FOR
            XML PATH(''),TYPE
    ).value(N'.','nvarchar(max)') , 4, 100000)
END, '') AS StudentHome,

Upvotes: 1

Related Questions