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 NOTNULL
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')ISNOT NULL
BEGIN
DROP TABLE [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME];
END
GO
CREATE TABLE [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]
(
CANDIDATEID INT NOT NULL IDENTITYPRIMARY 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.
TSQL trick only with FOR XML Support
Introduction
In this article we are going to demonstrate a TSQL trick only with FOR XML Support.
Case Study
We have three Table Objects
tbl_CUSTOMER
CUSTID
|
CUSTNAME
|
1
|
Joydeep Das
|
2
|
Chandan Bannerjee
|
3
|
Soumen Bhowmik
|
tbl_ITEMDTLS
ITEMCD
|
ITEMNAME
|
100
|
Tooth Paste
|
101
|
Tooth Brusg
|
102
|
Saving Lotion
|
103
|
Saving Brush
|
Now the customer purchase Items
tbl_SALEDTLS
SALENO
|
SRLNO
|
CUSTID
|
ITEMCD
|
201
|
1
|
1
|
100
|
201
|
2
|
1
|
101
|
201
|
3
|
1
|
102
|
201
|
4
|
1
|
103
|
202
|
1
|
2
|
100
|
202
|
2
|
2
|
101
|
203
|
1
|
3
|
100
|
We want a report like this Format
CUSTID
|
CUSTNAME
|
ITEM DETAILS
|
1
|
Joydeep Das
|
Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste
|
2
|
Chandan Bannerjee
|
Tooth Brusg, Tooth Paste
|
3
|
Soumen Bhowmik
|
Tooth Paste
|
1
|
Joydeep Das
|
Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste
|
How to solve it
-- Table Object Customer
IF OBJECT_ID(N'dbo.tbl_CUSTOMER', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_CUSTOMER];
END
GO
CREATE TABLE [dbo].[tbl_CUSTOMER]
(
CUSTID INT NOT NULL IDENTITY PRIMARY KEY,
CUSTNAME VARCHAR(50) NOT NULL
);
GO
-- Insert Records
INSERT INTO [dbo].[tbl_CUSTOMER]
(CUSTNAME)
VALUES('Joydeep Das'),
('Chandan Bannerjee'),
('Soumen Bhowmik');
-- Table Object Item Details
IF OBJECT_ID(N'dbo.tbl_ITEMDTL', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_ITEMDTL];
END
GO
CREATE TABLE [dbo].[tbl_ITEMDTL]
(
ITEMCD INT NOT NULL IDENTITY(100,1) PRIMARY KEY,
ITEMNAME VARCHAR(50) NOT NULL
)
GO
-- Insert Records
INSERT INTO [dbo].[tbl_ITEMDTL]
(ITEMNAME)
VALUES('Tooth Paste'),
('Tooth Brusg'),
('Saving Lotion'),
('Saving Brush');
-- Table Object Sales Dtls
IF OBJECT_ID(N'dbo.tbl_SALEDTLS', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_SALEDTLS];
END
GO
CREATE TABLE [dbo].[tbl_SALEDTLS]
(
SALENO INT NOT NULL,
SRLNO INT NOT NULL,
CUSTID INT NOT NULL,
ITEMCD INT NOT NULL,
CONSTRAINT PK_tbl_SALEDTLS PRIMARY KEY
(
SALENO ASC,
SRLNO ASC
)
)
GO
-- Insert Records
INSERT INTO [dbo].[tbl_SALEDTLS]
(SALENO, SRLNO, CUSTID, ITEMCD)
VALUES(201, 1, 1, 100),
(201, 2, 1, 101),
(201, 3, 1, 102),
(201, 4, 1, 103),
(202, 1, 2, 100),
(202, 2, 2, 101),
(203, 1, 3, 100);
GO
SELECT * FROM [dbo].[tbl_CUSTOMER];
SELECT * FROM [dbo].[tbl_ITEMDTL];
SELECT * FROM [dbo].[tbl_SALEDTLS];
-- Query
SELECT a.CUSTID, a.CUSTNAME,
STUFF((SELECT ', '+ y.ITEMNAME
FROM [dbo].[tbl_SALEDTLS] AS x
INNER JOIN [dbo].[tbl_ITEMDTL] AS y
ON x.ITEMCD = y.ITEMCD
WHERE x.CUSTID = a.CUSTID
ORDER BY ',' + y.ITEMNAME
FOR XML PATH('')),1,1,'') AS [ITEM DETAILS]
FROM [dbo].[tbl_CUSTOMER] AS a;
CUSTID CUSTNAME ITEM DETAILS
1 Joydeep Das Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste
2 Chandan Bannerjee Tooth Brusg, Tooth Paste
3 Soumen Bhowmik Tooth Paste
Hope you like it.
