在执行查询时，xml路径返回>的 >如何获得原始价值?

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.

我有一个查询，根据其中的条件生成一些文本。但是不要担心所有的情况，我只是在‘md.otherdrugs =‘otherdrugs’以及它的注释显示出来的时候遇到了问题。

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.

我期待着《舒伯汉姆>》。

2 个解决方案

#1

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

尝试使用value()方法将XML转换为Varchar (XML数据类型)

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.

以上将删除所有类型的XML标记，并将为您提供纯文本。

#2

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

& lt;和比;小于或大于xml的标记。您可以在查询中替换它们，以便:

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'')

#1