Over a million developers have joined DZone.

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

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

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!

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

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 best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

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

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

{{ parent.tldr }}

{{ parent.urlSource.name }}