In this article we will learn about How to converting Sql Server Data Table into XML Using XML AUTO, XML PATH, XML RAW and Custom XML.
Create Temp Student table
--Create Temp Student table
CREATE TABLE #tStudent
(
Id int IDENTITY (1, 1),
StudentName varchar(100),
Class varchar(20),
Section varchar(10),
RollNo varchar(5)
)
GO
Populate Student Table With Dummy Data
--Populate Student Table With Dummy Data
INSERT INTO #tStudent
VALUES ('Amit', '6', 'A', '1'), ('Amit Kumar', '6', 'A', '2'), ('Rohit Sharms', '6', 'A', '3'), ('Sanjeev Kuamar', '6', 'A', '4'), ('Devu', '6', 'A', '5')
GO
Displaying Student Data
--Displaying Student Data
SELECT * FROM #tStudent
GO
Id StudentName Class Section RollNo
----------- ------------------- -------------------- ---------- ------
1 Amit 6 A 1
2 Amit Kumar 6 A 2
3 Rohit Sharms 6 A 3
4 Sanjeev Kuamar 6 A 4
5 Devu 6 A 5
(5 rows affected)
1). Generate XML using AUTO
-- Generate XML using AUTO
DECLARE @xmlv xml
SELECT @xmlv = (SELECT * FROM #tStudent FOR xml AUTO, ROOT ('Student'))
SELECT @xmlv
Go
-- OUTPUT
<Student>
<_x0023_tStudent Id="1" StudentName="Amit" Class="6" Section="A" RollNo="1" />
<_x0023_tStudent Id="2" StudentName="Amit Kumar" Class="6" Section="A" RollNo="2" />
<_x0023_tStudent Id="3" StudentName="Rohit Sharms" Class="6" Section="A" RollNo="3" />
<_x0023_tStudent Id="4" StudentName="Sanjeev Kuamar" Class="6" Section="A" RollNo="4" />
<_x0023_tStudent Id="5" StudentName="Devu" Class="6" Section="A" RollNo="5" />
</Student>
2). Generate XML using PATH
-- Generate XML using PATH
DECLARE @xmlv xml
SELECT @xmlv = (SELECT * FROM #tStudent FOR xml PATH ('StudentDetails'), ROOT ('Student'))
SELECT @xmlv
Go
--OUTPUT
<Student>
<StudentDetails>
<Id>1</Id>
<StudentName>Amit</StudentName>
<Class>6</Class>
<Section>A</Section>
<RollNo>1</RollNo>
</StudentDetails>
<StudentDetails>
<Id>2</Id>
<StudentName>Amit Kumar</StudentName>
<Class>6</Class>
<Section>A</Section>
<RollNo>2</RollNo>
</StudentDetails>
<StudentDetails>
<Id>3</Id>
<StudentName>Rohit Sharms</StudentName>
<Class>6</Class>
<Section>A</Section>
<RollNo>3</RollNo>
</StudentDetails>
<StudentDetails>
<Id>4</Id>
<StudentName>Sanjeev Kuamar</StudentName>
<Class>6</Class>
<Section>A</Section>
<RollNo>4</RollNo>
</StudentDetails>
<StudentDetails>
<Id>5</Id>
<StudentName>Devu</StudentName>
<Class>6</Class>
<Section>A</Section>
<RollNo>5</RollNo>
</StudentDetails>
</Student>
3). Generate XML using RAW
-- Generate XML using RAW
DECLARE @xmlv xml
SELECT @xmlv = (SELECT * FROM #tStudent FOR xml RAW ('StudentDetails'), ROOT ('Student'))
SELECT @xmlv
Go
--OUTPUT
<Student>
<StudentDetails Id="1" StudentName="Amit" Class="6" Section="A" RollNo="1" />
<StudentDetails Id="2" StudentName="Amit Kumar" Class="6" Section="A" RollNo="2" />
<StudentDetails Id="3" StudentName="Rohit Sharms" Class="6" Section="A" RollNo="3" />
<StudentDetails Id="4" StudentName="Sanjeev Kuamar" Class="6" Section="A" RollNo="4" />
<StudentDetails Id="5" StudentName="Devu" Class="6" Section="A" RollNo="5" />
</Student>
4). Generate Custom XML Using Alias with @
-- Generate Custom XML Using Alias with @
DECLARE @xmlv xml
SELECT @xmlv = (
SELECT Id AS '@Id', StudentName AS '@StudentName', Class, Section, RollNo
FROM #tStudent
FOR xml PATH ('StudentDetails'), ROOT ('Student'))
SELECT @xmlv
Go
--OUTPUT
<Student>
<StudentDetails Id="1" StudentName="Amit">
<Class>6</Class>
<Section>A</Section>
<RollNo>1</RollNo>
</StudentDetails>
<StudentDetails Id="2" StudentName="Amit Kumar">
<Class>6</Class>
<Section>A</Section>
<RollNo>2</RollNo>
</StudentDetails>
<StudentDetails Id="3" StudentName="Rohit Sharms">
<Class>6</Class>
<Section>A</Section>
<RollNo>3</RollNo>
</StudentDetails>
<StudentDetails Id="4" StudentName="Sanjeev Kuamar">
<Class>6</Class>
<Section>A</Section>
<RollNo>4</RollNo>
</StudentDetails>
<StudentDetails Id="5" StudentName="Devu">
<Class>6</Class>
<Section>A</Section>
<RollNo>5</RollNo>
</StudentDetails>
</Student>
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.