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

Efficient Use of Grid Data When Saving In DB

Introduction

A data grid is a fundamental element for every developer. We have to save records from Grid to our Backend SQL Server Table object.

Here in this article we are trying to save the records from Data Grid to our Table object in efficient way. That is the entire data grid records all in together. Hope it will be interesting.

What we find in some development

Suppose we have data grid like this

Student Roll	Student Name	Student Class
1	Joydeep Das	1
2	Shipra Roy Chowdhury	1
3	Deepasree Das	1

To save the records in the Database we have a Stored procedure like this

CREATE PROCEDURE [dbo].[proc_SaveRecord]

         (

               @p_idRoll        INT,

               @p_cStdName      VARCHAR(50),

               @p_iStdClass     INT

         )

   AS

   BEGIN

      ......

       .....

   END

This type of common practice stored procedure have parameters for all the columns in the table and the developer pass the value from grid to the parameters one by one by some sort of looping and save the data in the database.

Bad practice as the frontend frequently communicates with backend and the cost is so high. This type of practice make or increasing BLOACKING and the result we find the TIME OUT in case of Web Application.

What the Suggested Method to do that

We can pass the entire data table into our stored procedure and save the records in our database table

Example of Best Practice

Step – 1 [ Create the Base Table to Save Records from Grid ]

CREATE TABLE tbl_Student

   (

      idRoll    INT         NOT NULL IDENTITY PRIMARY KEY,

      cStdName  VARCHAR(50) NOT NULL,

      iStdClass INT         NOT NULL

  );

Step – 2 [ Create the Stored Procedure to Accept XML String ]

CREATE PROCEDURE [dbo].[proc_SaveRecord]

         (

             @p_GridData   XML

         )

   AS

BEGIN

     DECLARE @Handle AS INT;

      CREATE TABLE #tmpStudent

           (

                  idRoll     INT           NOT NULL,

                  cStdName   VARCHAR(50)   NOT NULL,

                  iStdClass  INT           NOT NULL

           )

      EXEC sp_xml_preparedocument

            @Handle OUTPUT,

             @p_GridData;

     INSERT INTO #tmpStudent

     SELECT *

     FROM   OPENXML (@Handle, '/Dataset/tbl_Student', 1)

            WITH

            (

              idRoll       INT,

              cStdName     VARCHAR(50),

              iStdClass    INT

            );

     EXEC sp_xml_removedocument @Handle;

     -- Actual Save --

     INSERT INTO tbl_Student

            (cStdName, iStdClass)

     SELECT cStdName, iStdClass

     FROM   #tmpStudent;

END

Step – 3 [ Now we have to Concert Grid Data into XML String ]

<Dataset>

<tbl_Student idRoll="1" cStdName="Joydeep Das" iStdClass="1" />

<tbl_Student idRoll="2" cStdName="Shipra Roy Chowdhury" iStdClass="1" />

<tbl_Student idRoll="3" cStdName="Deepasree Das" iStdClass="1" />

</Dataset>

Step-4 [ Calling the Stored Procedure with XML ]

BEGIN

     DECLARE @p_GridData AS XML;

     SET @p_GridData =

'<Dataset>

<tbl_Student idRoll="1" cStdName="Joydeep Das" iStdClass="1" />

<tbl_Student idRoll="2" cStdName="Shipra Roy Chowdhury" iStdClass="1" />

<tbl_Student idRoll="3" cStdName="Deepasree Das" iStdClass="1" />

</Dataset>

';

      

      EXEC [dbo].[proc_SaveRecord]

           @p_GridData = @p_GridData;

END

Step-5 [ Observe the Output ]

SELECT * FROM tbl_Student;

idRoll  cStdName                              iStdClass

1          Joydeep Das                           1

2          Shipra Roy Chowdhury           1

3          Deepasree Das                       1

There are other alternate way for that. If have you nay other best way to Save grid records in a table, please share your knowledge with us.

Hope you like it.

Posted by: JOYDEEP DAS

Simple XML Tips

Introduction

This article contains the simple XML tips of SQL Server to Represent Data.

Understand the problem

We have two table objects

--- Parent Table

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

   BEGIN

       DROP TABLE [dbo].[tbl_EDUCATIONALINSTITUTE];

   END

GO

CREATE TABLE [dbo].[tbl_EDUCATIONALINSTITUTE]

       (

          IDNO           INT          NOT NULL PRIMARY KEY,

          INSTITUTENAME  VARCHAR(100) NOT NULL

        )

GO

INSERT INTO [dbo].[tbl_EDUCATIONALINSTITUTE]

       (IDNO, INSTITUTENAME)

VALUES (101, 'ABC-Educare'),

       (102, 'SQL Knowledge Bank'),

       (103, 'A to Z Computer Education');

GO

--- Child Table

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

   BEGIN

       DROP TABLE [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME];

   END

GO

CREATE TABLE [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]

       (

          CANDIDATEID      INT          NOT NULL IDENTITY PRIMARY KEY,

          IDNO             INT          NOT NULL,

          CANDIDATENAME    VARCHAR(50)  NOT NULL,

          HIGHESTEDUCATION VARCHAR(50)  NOT NULL,

          EXPERIENCEYEAR   INT          NOT NULL

        )

GO

--- Foreign Key Relation

ALTER TABLE [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]

ADD CONSTRAINT FK_tbl_EDUCATIONALINSTITUTE_RESUME_IDNO

FOREIGN KEY(IDNO)

REFERENCES [dbo].[tbl_EDUCATIONALINSTITUTE](IDNO);

GO

INSERT INTO [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]

       (IDNO, CANDIDATENAME, HIGHESTEDUCATION, EXPERIENCEYEAR)

VALUES (101, 'Sukamal Jana', 'MCA', 9),

       (101, 'Anirudha Dey', 'B.Tech', 5),

       (102, 'Joydeep Das', 'MCDBA', 11),

       (102, 'Deepasree Das', 'B.E', 5),

       (103, 'Arabind Sarkar', 'B.E', 5),

       (103, 'Sudip Das', 'M.tech', 15);

GO

What happens After a JOIN

SELECT a.IDNO AS [INSTITUTE ID], a.INSTITUTENAME, b.CANDIDATEID,  

       b.CANDIDATENAME,

       b.HIGHESTEDUCATION, b.EXPERIENCEYEAR

FROM   [dbo].[tbl_EDUCATIONALINSTITUTE] AS a

       INNER JOIN [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME] AS b

                    ON a.IDNO = b.IDNO;

Output:

What Actually We Want

INSTITUTE ID	INSTITUTE NAME	CANDIDATE DETAILS
101	ABC-Educare	XML Script
102	SQL Knowledge Bank	XML Script
103	A to Z Comouter Education	XML Script

So the Institute name is not replicated and the Institute name must appears once. All the candidate within the specified institute must appear in XML Scripts with all details of candidate.

How we can Solve it

SELECT a.IDNO AS [INSTITUTE ID], a.INSTITUTENAME,

       (SELECT CANDIDATEID, CANDIDATENAME, HIGHESTEDUCATION,                             EXPERIENCEYEAR

        FROM   [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]

        WHERE  IDNO = a.IDNO

        FOR XML RAW('Candidat'), ROOT('DataSet'), ELEMENTS) 

                  AS [CANDIDATE DETAILS]

FROM   [dbo].[tbl_EDUCATIONALINSTITUTE] AS a;

Hope you like it.

Posted by: MR. JOYDEEP DAS