SQL Server "FOR XML" encoding HTML entities?

Question

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

You should have a standard library in any language you are using that will convert the html entities back — Hogan
– Hogan, Commented Oct 17, 2019 at 17:55
Rather than SUBSTRING it's often better to use STUFF the remove characters from the start of a string. — Thom A
– Thom A ♦, Commented Oct 17, 2019 at 17:57
Because, if the OP did have more than 10000 character's it would be truncated. STUFF would simply be used to replace the first characters, it wouldn't be able to truncate it, @Hogan . — Thom A
– Thom A ♦, Commented Oct 17, 2019 at 18:50

PeterHe · Accepted Answer · 2019-10-17 18:33:09Z

1

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,

answered Oct 17, 2019 at 18:33

PeterHe

2,7861 gold badge11 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server "FOR XML" encoding HTML entities?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related