Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Simple XML Tips

DZone's Guide to

Simple XML Tips

With two table objects, we'll use XML in SQL Server to represent the data via JOIN and some troubleshooting in code snippets for possible errors.

· Database Zone
Free Resource

Download the Guide to Open Source Database Selection: MySQL vs. MariaDB and see how the side-by-side comparison of must-have features will ease the journey. Brought to you in partnership with MariaDB.

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 we actually 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 liked it!

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.

Topics:
sql ,xml ,sql server ,database

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}