xml path returns "&lt;" for < and "&gt;" for > while executing query. how to get original value?

Question

I have a query which results some text on the basis of condition in it. but do not worry about all the conditions, I am only facing issue when 'md.OtherMedication = 'OTHERMEDICATION' and its comment to be shown.

So if comment have value like <shubham> then it is returning

 <shubham>

which is not a expected result. following is the query i am using.

Select '' + CASE WHEN md.OtherMedication = 'OTHERMEDICATION' THEN md.Comment ELSE '' END
        FROM Medication md
        WHERE md.HraDiagnosisId = 94121 FOR XML PATH(N'')

I am expecting <shubham> as result.

Answer 1

< and > are less than and greater than tags of your xml. you can replace them in your query so:

Select '' + CASE WHEN md.OtherMedication = 'OTHERMEDICATION' 
THEN Replace(Replace(md.Comment, '&lt;', '<'), '&gt;', '>') ELSE '' END
FROM Medication md
WHERE md.HraDiagnosisId = 94121 FOR XML PATH(N'')

Answer 2

Try casting XML to Varchar with value() Method (xml Data Type)

select (Select '' + CASE WHEN md.OtherMedication = 'OTHERMEDICATION' 
                    THEN md.Comment ELSE '' END
        FROM Medication md
        WHERE md.HraDiagnosisId = 94121 FOR XML PATH(N''),TYPE).value('.','varchar(max)')

The above will remove all types of XML Tags and will give you plain text.