Receiving error: The xml data type cannot be selected as DISTINCT because it...

• Goalie35

  SSC Eights!

  Points: 871

  More actions

  March 2, 2020 at 2:55 am

  Go to Answer

  #3730006

  In the below query, the data returned in the query is in xml format.  I'm receiving an issue with duplicate records being returned however, so I wanted to set it to "Select DISTINCT...", however in doing so, I'm now receiving this error, but I'm not sure how to correct it to ensure I still receive distinct records:

  The xml data type cannot be selected as DISTINCT because it is not comparable.

  Here's my query, which currently contains the "Select distinct", which is throwing the error.  Any idea what I can do to ensure only distinct records are returned?

  select distinct af.id id,
                  
  (
  select
  TypeId TypeId
  for xml path(''), type) FileType,
  CreatedDate,
  CreatedBy
  fromTabelA af
  where
  order by CreatedDate,af.id
  for xml path('MyFile'), root('ArrayOfMyFile'), type

  Thanks

  • This topic was modified 4 years, 8 months ago by  Goalie35.
  • This topic was modified 4 years, 8 months ago by  Goalie35.

• sterling3721

  SSChasing Mays

  Points: 658

  More actions

  March 2, 2020 at 3:15 am

  Answer

  #3730008

  you can cast all xml columns to nvarchar(max) then use distinct on nvarchar(max).

• March 2, 2020 at 6:35 am

  This was removed by the editor as SPAM