SQL查询Case语句不检查XML数据类型列 [英] Sql query Case statement does not check xml data type column

问题描述

大家好，

我遇到一个错误，例如带有带有评估XML数据类型列的SQL Query Select Case语句，但是我试图花费更多的时间来获得类似的错误.

错误:无法对XML数据类型进行比较或排序，除非使用IS NULL运算符.

这是我的SQL查询:-
选择TOP 1000 [AccountsPayable_Content] .ItemID，[AccountsPayable_Content] .C2SID_SUBJECT，[AccountsPayable_Content] .C2SID_CREATOR，
[AccountsPayable_Content] .C2SID_DATE，[AccountsPayable_Content] .KEYASE，CASE [AccountsPayable_Content] .C2SID_Notes，当ISNULL([AccountsPayable_Content] .C2SID_Notes)时为"0"，否则为"1"，
[AccountsPayable_Content] .LastName" FROM AccountsPayable_Content

Hi all,

i have face one error like SQL Query Select Case statement with evaluate XML data type columns, but i was trying to more than time to get the error like.

ERROR:The XML data type cannot be compared or sorted, except when using the IS NULL operator.

this is my SQL query:-
Select TOP 1000 [AccountsPayable_Content].ItemID,[AccountsPayable_Content].C2SID_SUBJECT, [AccountsPayable_Content].C2SID_CREATOR,
[AccountsPayable_Content].C2SID_DATE, [AccountsPayable_Content].KEYWORDS,CASE [AccountsPayable_Content].C2SID_Notes WHEN ISNULL([AccountsPayable_Content].C2SID_Notes)THEN "0" else "1" end,
[AccountsPayable_Content].LastName" FROM AccountsPayable_Content

推荐答案

您无法按XML或TEXT列进行分组，必须首先将其转换为varchar(max).

试试:

You cannot group by XML or TEXT columns, you have to first convert it to varchar(max).

Try:

SELECT 
    TOP 1000 [AccountsPayable_Content].ItemID,
    [AccountsPayable_Content].C2SID_SUBJECT, 
    [AccountsPayable_Content].C2SID_CREATOR,
    [AccountsPayable_Content].C2SID_DATE, 
    [AccountsPayable_Content].KEYWORDS,
    CASE [AccountsPayable_Content].C2SID_Notes 
       WHEN ISNULL(CONVERT(xml,CONVERT(varchar(max),[AccountsPayable_Content].C2SID_Notes)) NotesXML)
       THEN 
            "0" 
       ELSE
            "1" 
    END,
    [AccountsPayable_Content].LastName" 
FROM 
    AccountsPayable_Content

此处的详细信息: xml数据类型的限制 [ ^ ]

Details here: Limitations of the xml Data Type[^]

这篇关于SQL查询Case语句不检查XML数据类型列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！