I have the following data table:

data table
Image 1.

Now I will show this table data in a XML group by class like the following:

XML Code
Image 2.

My Data Table in Design mode:

table design
Image 3.

The script of my table is:

  1. CREATE TABLE [dbo].[Student](
  2. [StudentID] [int] IDENTITY(1,1) NOT NULL,
  3. [Name] [varchar](50) NULL,
  4. [Email] [varchar](500) NULL,
  5. [Class] [varchar](50) NULL,
  6. [EnrollYear] [varchar](50) NULL,
  7. [City] [varchar](50) NULL,
  8. [Country] [varchar](50) NULL,
  9. CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
  10. (
  11. [StudentID] ASC
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  13. ) ON [PRIMARY]
  15. GO
Now write the following SQL Query:

SQL Query
Image 4.
  1. DECLARE @MyTempTable TABLE
  2. (
  3. Class VARCHAR(30),
  4. Name VARCHAR(30),
  5. EnrollYear VARCHAR(20),
  6. City VARCHAR(50)
  7. )
  9. INSERT INTO @MyTempTable (Class,Name,EnrollYear,City)
  10. SELECT Class,Name,EnrollYear,City FROM Student
  12. SELECT T1.Class AS '@ClassName',
  13. (
  14. SELECT T2.Name AS '@Name',
  15. T2.EnrollYear As '@EnrollYear',
  16. T2.City AS '@City'
  17. FROM @MyTempTable AS T2
  18. WHERE T2.Class = T1.Class
  19. FOR XML PATH('StudentInfo'), TYPE
  20. )
  22. FROM @MyTempTable AS T1
  23. GROUP BY T1.Class
  24. FOR XML PATH('Class'), ROOT('StudentBySubject')
Now run your query.

Run your Query
Image 5.

See your XML.

XML data
Image 6.

You can save your XML file like the following:

save file
Image 7.