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

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

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!

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

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.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}