In the previous article we saw the Merge Statement.

These articles will work around the merge statement over XML data type. We know that the XML data type SQL SERVER. The XML Data type stores XML data using Bulk Insert of the 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
  3. --no record in Student Table
  5. Merge Statement usign Xml Data.
  7. Merge into Student as Trg
  8. Using (select d.x.value('Name[1]','varchar(20)') as Name ,
  9. d.x.value('Marks[1]','int') as Marks from
  10. @data.nodes('/Root/Student')as d(x)) as Src
  11. on Trg.Name=Src.Name
  12. When Matched Then update set
  13. Trg.Marks=Src.Marks
  14. when not matched by target then
  15. insert (Name,Marks) values(Src.Name,Src.Marks);
  18. select * from Student
Here all the rows are 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>'
  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


Remove some data from 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