Show SQL Server Data Table in a XML Using SQL Query

In this article I explain how to show our SQL Server data table in a XML format using SQL Query.

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  = ONON [PRIMARY]  
  13. ON [PRIMARY]  
  14.   
  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. )  
  8.   
  9.  INSERT INTO @MyTempTable (Class,Name,EnrollYear,City)  
  10.  SELECT Class,Name,EnrollYear,City FROM Student  
  11.    
  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. )  
  21.   
  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.