SELECT DISTINCT and GROUP BY not Work With XML Columns

Introduction

When we use the SELECT statement with XML columns the DISTINCT clause and GROUP BY clause is not working. This article is related to alteration solution of this problem. Hope you find it informative.

Example

To understand it properly let’s take an example

Step-1[ Create Base table and Insert Records in it ]

IF OBJECT_ID(N'[dbo].[tbl_DemoData]', N'U')IS NOT NULL

   BEGIN

        DROP TABLE [dbo].[tbl_DemoData];

   END

GO

CREATE TABLE [dbo].[tbl_DemoData]

   (

     IDNO         INT               NOT NULL,

       SNAME            VARCHAR(50) NOT NULL,

       DETAILS    XML               NOT NULL

   );

GO

INSERT INTO [dbo].[tbl_DemoData]

       (IDNO, SNAME, DETAILS)

VALUES

(1, 'Joydeep Das', '<_x0023_AAA Name="Joydeep Das" CLass="1" />'),

(1, 'Joydeep Das', '<_x0023_AAA Name="Joydeep Das" CLass="1" />'),

(1, 'Joydeep Das', '<_x0023_AAA Name="Joydeep Das" CLass="1" />'),

(2, 'Avijit Ghorui', '<_x0023_AAA Name="Avijit Ghorui" CLass="1" />'),

(2, 'Avijit Ghorui', '<_x0023_AAA Name="Avijit Ghorui" CLass="1" />'),

(2, 'Avijit Ghorui', '<_x0023_AAA Name="Avijit Ghorui" CLass="1" />');

GO

SELECT IDNO, SNAME, DETAILS FROM [dbo].[tbl_DemoData];

GO

IDNO        SNAME             DETAILS

1           Joydeep Das       <_x0023_AAA Name="Joydeep Das" CLass="1" />

1           Joydeep Das       <_x0023_AAA Name="Joydeep Das" CLass="1" />

1           Joydeep Das       <_x0023_AAA Name="Joydeep Das" CLass="1" />

2           Avijit Ghorui     <_x0023_AAA Name="Avijit Ghorui" CLass="1" />

2           Avijit Ghorui     <_x0023_AAA Name="Avijit Ghorui" CLass="1" />

2           Avijit Ghorui     <_x0023_AAA Name="Avijit Ghorui" CLass="1" />

Step-2[ Try to Use GROUP BY and DISTINCT Clause ]

SELECT IDNO, SNAME, DETAILS

FROM   [dbo].[tbl_DemoData]

GROUP BY IDNO, SNAME, DETAILS;

Error:

Msg 305, Level 16, State 1, Line 38

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

SELECT DISTINCT IDNO, SNAME, DETAILS

FROM   [dbo].[tbl_DemoData];

Error:

Msg 421, Level 16, State 1, Line 43

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

Step-3 [ Solution for GROUP BY Clause ]

SELECT IDNO, SNAME, CONVERT(XML, CONVERT(VARCHAR(Max), DETAILS)) AS DETAILS 

FROM   [dbo].[tbl_DemoData]

GROUP BY IDNO, SNAME, CONVERT(VARCHAR(Max), DETAILS);

IDNO  SNAME             DETAILS

1     Joydeep Das       <_x0023_AAA Name="Joydeep Das" CLass="1" />

2     Avijit Ghorui     <_x0023_AAA Name="Avijit Ghorui" CLass="1" />

Step-4 [ Solution for DISTINCT Clause ]

WITH myDistinct

AS

(

   SELECT ROW_NUMBER() OVER(PARTITION BY IDNO ORDER BY IDNO) AS SRL,
          IDNO, SNAME, DETAILS

   FROM   [dbo].[tbl_DemoData] 

)

SELECT IDNO, SNAME, DETAILS  FROM myDistinct WHERE  SRL=1;

IDNO  SNAME             DETAILS

1     Joydeep Das       <_x0023_AAA Name="Joydeep Das" CLass="1" />

2     Avijit Ghorui     <_x0023_AAA Name="Avijit Ghorui" CLass="1" />

Hope you like it.

Posted By: MR. JOYDEEP DAS