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

  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  
  3.   
  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);  
  10.   
  11.   
  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  
  2.   
  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>'  
  18.   
  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);  
  28.   
  29.   
  30. select * from Student  


The following will remove some data from the XML (“Here GoWtham“ record):
  1. Declare @Data xml  
  2.   
  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>'  
  14.   
  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;  
  25.   
  26.   
  27. select * from Student