In this Article, we will learn How to save xml data into SQL Server table.
First of all, we will create temp table, to insert data from xml.
--Create Temp Student table
CREATE TABLE #tStudent
(
Id int,
StudentName varchar(100),
Class varchar(20),
Section varchar(10),
RollNo varchar(5)
)
GO
Create an xml variable with xml data.
-- XML Data
declare @v xml='<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>'
Inserting data into #tStudent from XML variable @v
-- INSETING DATA FROM XML
INSERT #tStudent
SELECT t.c.value('@Id', 'int') as Id,
t.c.value('@StudentName', 'varchar(100)') as StudentName,
t.c.value('@Class', 'varchar(20)') as Class,
t.c.value('@Section', 'varchar(10)') as Section,
t.c.value('@RollNo', 'varchar(5)') as RollNo
FROM @v.nodes('/Student/StudentDetails') AS t(c)
Go
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.