Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Show SQL Server Table Data in a XML using SQL Query

Article:
Viewed:  839 
Posted On:  02/12/2014 19:37:15 
How we can show SQL Server table Data in a XML using SQL Query group by a column ? 

 

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:

1.png

Image 1.

Now I will show this table data in XML group by Class like below:

2.png

Image 2.

My Data Table in Design mode:

3.png

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:

4.png

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

5.png

Image 5.

See your XML.

6.png

Image 6.

You can save your XML file like below:

7.png

Image 7.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us