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