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.
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.
<
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, '<', '<'), '>', '>') ELSE '' END
FROM Medication md
WHERE md.HraDiagnosisId = 94121 FOR XML PATH(N'')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With