Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

 &lt;shubham&gt;

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.

like image 267
shubham bahuguna Avatar asked Dec 26 '16 12:12

shubham bahuguna


2 Answers

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.

like image 180
Shakeer Mirza Avatar answered Nov 14 '22 21:11

Shakeer Mirza


&lt; and &gt; 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'')
like image 25
Tedo G. Avatar answered Nov 14 '22 21:11

Tedo G.