I have the following data table:
Image 1.
Now I will show this table data in a XML group by class like the following:
Image 2.
My Data Table in Design mode:
Image 3.
The 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 the following 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 the following:
Image 7.