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
  1. --Create Student Table
  2. if object_id('Student') is null
  3. create table Student(id int identity(1,1) ,Name varchar(20), Marks int)

  1. Declare Xml Data Type and Assign Some Xml Data.
  2. Declare @Data xml
  4. set @Data=
  5. '<Root>
  6. <Student>
  7. <Name>Rakesh</Name>
  8. <Marks>80</Marks>
  9. </Student>
  10. <Student>
  11. <Name>Mahesh</Name>
  12. <Marks>90</Marks>
  13. </Student>
  14. <Student>
  15. <Name>Gowtham</Name>
  16. <Marks>60</Marks>
  17. </Student>
  18. </Root>'
  19. select @Data as StudentData

  1. select * from Student
  2. --no record in Student Table
Merge Statement usign XML Data
  1. Merge into Student as Trg
  2. Using (select d.x.value('Name[1]','varchar(20)') as Name ,
  3. d.x.value('Marks[1]','int') as Marks from
  4. @data.nodes('/Root/Student')as d(x)) as Src
  5. on Trg.Name=Src.Name
  6. When Matched Then update set
  7. Trg.Marks=Src.Marks
  8. when not matched by target then
  9. insert (Name,Marks) values(Src.Name,Src.Marks);
  12. 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.
  1. Declare @Data xml
  3. set @Data=
  4. '<Root>
  5. <Student>
  6. <Name>Rakesh</Name>
  7. <Marks>60</Marks>
  8. </Student>
  9. <Student>
  10. <Name>Mahesh</Name>
  11. <Marks>90</Marks>
  12. </Student>
  13. <Student>
  14. <Name>Gowtham</Name>
  15. <Marks>80</Marks>
  16. </Student>
  17. </Root>'
  19. Merge into Student as Trg
  20. Using (select d.x.value('Name[1]','varchar(20)') as Name
  21. ,d.x.value('Marks[1]','int') as Marks from
  22. @data.nodes('/Root/Student')as d(x)) as Src
  23. on Trg.Name=Src.Name
  24. When Matched Then update set
  25. Trg.Marks=Src.Marks
  26. when not matched by target then
  27. insert (Name,Marks) values(Src.Name,Src.Marks);
  30. select * from Student


The following will remove some data from the XML (“Here GoWtham“ record):
  1. Declare @Data xml
  3. set @Data=
  4. '<Root>
  5. <Student>
  6. <Name>Rakesh</Name>
  7. <Marks>60</Marks>
  8. </Student>
  9. <Student>
  10. <Name>Mahesh</Name>
  11. <Marks>90</Marks>
  12. </Student>
  13. </Root>'
  15. Merge into Student as Trg
  16. Using (select d.x.value('Name[1]','varchar(20)') as Name
  17. ,d.x.value('Marks[1]','int') as Marks from
  18. @data.nodes('/Root/Student')as d(x)) as Src
  19. on Trg.Name=Src.Name
  20. When Matched Then update set
  21. Trg.Marks=Src.Marks
  22. when not matched by target then
  23. insert (Name,Marks) values(Src.Name,Src.Marks)
  24. when not matched by source then Delete;
  27. select * from Student