SQL Server Performance

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

Discussion in 'SQL Server 2005 General Developer Questions' started by Guvera, Sep 12, 2011.

  1. Guvera New Member

    Hi,

    I have a 3 tables and get the values using RIGHT OUTER JOIN for my requirements.

    I can get the correct value as what i need. But values are coming with duplication. So avoid duplication i will put group by for two columns that datatype is xml. I got the below error when i select the query. I already saw the solution in this forum. But it is not working for me. I got the same problem. Hope you understand my problem.

    Msg 305, Level 16, State 1, Line 2
    The xml data type cannot be compared or sorted, except when using the IS NULL operator.

    Regards
    Guvera
  2. preethi Member

    From MSDN (Books OnLine):
    http://msdn.microsoft.com/en-us/library/ms187107(v=sql.90).aspx

    You need to convert to another data type and then perform the operation.
    But generally xml data type is quite large. Using them in Group BY, CONVERT etc tend to take more memory. Do you really want to have the column in the select statement.
    You can perform a distinct select on the other columns, and find the primary key and then connect to this table to get the xml column.
    Please check whether that option performs better.

Share This Page