In this article I am going to explain how we can show our SQL serve data table in a XML format using SQL Query.
I have below data table:
Image 1.
Now I will show this table data in XML group by Class like below:
Image 2.
My Data Table in Design mode:
Image 3.
Script of my table is:
CREATE TABLE [dbo].[Student](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Email] [varchar](500) NULL,
[Class] [varchar](50) NULL,
[EnrollYear] [varchar](50) NULL,
[City] [varchar](50) NULL,
[Country] [varchar](50) NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
PRIMARY]
) ON [PRIMARY]
GO
Now write below SQL Query:
Image 4.
DECLARE @MyTempTable TABLE
(
Class VARCHAR(30),
Name VARCHAR(30),
EnrollYear VARCHAR(20),
City VARCHAR(50)
)
INSERT INTO @MyTempTable (Class,Name,EnrollYear,City)
SELECT Class,Name,EnrollYear,City FROM Student
SELECT T1.Class AS '@ClassName',
(
SELECT T2.Name AS '@Name',
T2.EnrollYear As '@EnrollYear',
T2.City AS '@City'
FROM @MyTempTable AS T2
WHERE T2.Class = T1.Class
FOR XML PATH('StudentInfo'), TYPE
)
FROM @MyTempTable AS T1
GROUP BY T1.Class
FOR XML PATH('Class'), ROOT('StudentBySubject')
Now Run your Query
Image 5.
See your XML.
Image 6.
You can save your XML file like below:
Image 7.