Merge Statement Using XML Data in SQL Server

In the last article we saw about the Merge Statement.

These articles will work around the merge statement over the XML data type. We know about the XML data type of SQL Server. The XML Data type stores XML data using Bulk Insert Data into SQL Tables.

Example

--Create Student Table
if object_id('Student') is null
create table Student(id int identity(1,1) ,Name varchar(20), Marks int)

Declare Xml Data Type and Assign Some Xml Data.
Declare @Data xml

set @Data=
'<Root>
<Student>
<Name>Rakesh</Name>
<Marks>80</Marks>
</Student>
<Student>
<Name>Mahesh</Name>
<Marks>90</Marks>
</Student>
<Student>
<Name>Gowtham</Name>
<Marks>60</Marks>
</Student>
</Root>'
select @Data as StudentData

select * from Student
--no record in Student Table

Merge Statement usign XML Data

Merge into Student as Trg
Using (select d.x.value('Name[1]','varchar(20)') as Name ,
d.x.value('Marks[1]','int') as Marks from
@data.nodes('/Root/Student')as  d(x)) as Src
on Trg.Name=Src.Name
When Matched Then update set
Trg.Marks=Src.Marks
when not matched by target then
insert (Name,Marks) values(Src.Name,Src.Marks);


select * from Student

Here all the rows have been inserted because no matching records existed in the Student table with the Name Key.



This time I changed the XML Data Marks column with the same data. This time we need to update the Student table data.

Declare @Data xml

set @Data=
'<Root>
<Student>
<Name>Rakesh</Name>
<Marks>60</Marks>
</Student>
<Student>
<Name>Mahesh</Name>
<Marks>90</Marks>
</Student>
<Student>
<Name>Gowtham</Name>
<Marks>80</Marks>
</Student>
</Root>'

Merge into Student as Trg
Using (select d.x.value('Name[1]','varchar(20)') as Name
,d.x.value('Marks[1]','int') as Marks from
@data.nodes('/Root/Student')as  d(x)) as Src
on Trg.Name=Src.Name
When Matched Then update set
Trg.Marks=Src.Marks
when not matched by target then
insert (Name,Marks) values(Src.Name,Src.Marks);


select * from Student

The following will remove some data from the XML (“Here GoWtham“ record):

Declare @Data xml

set @Data=
'<Root>
<Student>
<Name>Rakesh</Name>
<Marks>60</Marks>
</Student>
<Student>
<Name>Mahesh</Name>
<Marks>90</Marks>
</Student>
</Root>'

Merge into Student as Trg
Using (select d.x.value('Name[1]','varchar(20)') as Name
,d.x.value('Marks[1]','int') as Marks from
@data.nodes('/Root/Student')as d(x)) as Src
on Trg.Name=Src.Name
When Matched Then update set
Trg.Marks=Src.Marks
when not matched by target then
insert (Name,Marks) values(Src.Name,Src.Marks)
when not matched by source then Delete;


select * from Student