Bulk Insert, Update XML Data Into SQL Table

Introduction

Many times, while developing any software, our clients give us data into a bulk format or need a functionality of grid which contains many records. When the user edits multiple records from that grid, it needs to update all the records at a time into a database on the click of Submit button.
 
In this article, we are going to learn -
  1. Insert single record using XML into a SQL Table
  2. Insert multiple records using XML into a SQL Table
  3. Insert / Update multiple records using XML into a SQL Table at the same time
In this article, I have created a sample XML format. Using nodes() method to shred XML into multiple rows, which propagates parts of XML documents into row sets. Here I am not going to use any stored procedure or any user interface from which user submits the data. I just demonstrated in a simple way using SQL. If you like to modify this code as per your requirement.
 
Working with the XML Data Type in SQL Server

The XML data type, introduced in SQL Server 2005, is a powerful construct. When used wisely, it can provide useful extensions to SQL Server. Robert Sheldon, in the first part of a series, describes how to create an index a typed XML column in a table, and discusses when you should consider using an XML Datatype

Part 1

Suppose the user is passing data from a function in XML format which contains some employee information having properties like TravelId, TravelCity etc. In below code, I am using temp table #Travel. XML contains a single record. Using SQL code insert this XML data into a temp table. 
  1. DECLARE @EmployeeXML XML;  
  2. SET @EmployeeXML='<?xml version="1.0"?><EmpDetail>  
  3.                           <TravelId>1</TravelId><TravelCity>Pune</TravelCity>  
  4.                           </EmpDetail>';  
  5.     IF OBJECT_ID('tempdb..#Travel'is not null    
  6.     Begin    
  7.         Drop Table #Travel    
  8.     End    
  9.   
  10.   CREATE TABLE #Travel(TravelId int, TravelCity varchar(100))    
  11.   
  12. INSERT INTO  #Travel([TravelId],[TravelCity])  
  13. SELECT COALESCE([Table].[Column].value('TravelId[1]''int'),0) as 'TravelId',  
  14.           [Table].[Column].value('TravelCity[1]''varchar(100)'as 'TravelCity'  
  15.           FROM @EmployeeXML.nodes('/EmpDetail'as [Table]([Column])  
  16.             
  17. SELECT * FROM #Travel  
Output when we execute select command as,
 
Data Type in SQL Server 

Part 2

In below code XML contains two records. Using SQL code insert this XML data into a temp table.
  1. DECLARE @EmployeeXML XML;  
  2. SET @EmployeeXML='<?xml version="1.0"?>  
  3.                        <EmpDetails>  
  4.                             <EmpDetail>  
  5.                                         <TravelId>1</TravelId><TravelCity>Pune</TravelCity>  
  6.                             </EmpDetail>  
  7.                             <EmpDetail>  
  8.                                         <TravelId>2</TravelId><TravelCity>Mumbai</TravelCity>  
  9.                             </EmpDetail>  
  10.                         </EmpDetails>';  
  11.     IF OBJECT_ID('tempdb..#Travel'is not null    
  12.     Begin    
  13.         Drop Table #Travel    
  14.     End    
  15.   
  16.     CREATE TABLE #Travel(TravelId int, TravelCity varchar(100))   
  17.   
  18. INSERT INTO  #Travel([TravelId],[TravelCity])  
  19. SELECT COALESCE([Table].[Column].value('TravelId[1]''int'),0) as 'TravelId',  
  20.           [Table].[Column].value('TravelCity[1]''varchar(100)'as 'TravelCity'  
  21.           FROM @EmployeeXML.nodes('/EmpDetails/EmpDetail'as [Table]([Column])  
  22.             
  23. SELECT * FROM #Travel  
Output when we execute select command as,
 
Data Type in SQL Server
 
Part 3

Suppose the user is passing data from the function in XML which contains old data as well as some new record. Now user would like to update old data & if the record does not into a table then insert that record.
 
Here I am using @EmployeeXML_Old XML to insert data which contains some records and @EmployeeXML contains updated data as well as new data, I am using match condition to update the data as well as insert the data. 
  1.     IF OBJECT_ID('tempdb..#Travel'is not null    
  2.     Begin    
  3.         Drop Table #Travel    
  4.     End   
  5.   
  6. CREATE TABLE #Travel(TravelId int, TravelCity varchar(100))   
  7.   
  8. DECLARE @EmployeeXML_Old XML;  
  9. SET @EmployeeXML_Old='<?xml version="1.0"?>  
  10.                        <EmpDetails>  
  11.                             <EmpDetail>  
  12.                                         <TravelId>1</TravelId><TravelCity>Pune</TravelCity>  
  13.                             </EmpDetail>  
  14.                             <EmpDetail>  
  15.                                         <TravelId>2</TravelId><TravelCity>Mumbai</TravelCity>  
  16.                             </EmpDetail>  
  17.                         </EmpDetails>';  
  18.   
  19. INSERT INTO  #Travel([TravelId],[TravelCity])  
  20. SELECT COALESCE([Table].[Column].value('TravelId[1]''int'),0) as 'TravelId',  
  21.                   [Table].[Column].value('TravelCity[1]''varchar(100)'as 'TravelCity'  
  22.                   FROM @EmployeeXML_Old.nodes('/EmpDetails/EmpDetail'as [Table]([Column])  
  23.             
  24. SELECT * FROM #Travel  
  25.   
  26. DECLARE @EmployeeXML XML;  
  27. SET @EmployeeXML='<?xml version="1.0"?>  
  28.                        <EmpDetails>  
  29.                             <EmpDetail>  
  30.                                         <TravelId>1</TravelId><TravelCity>Pune Region</TravelCity>  
  31.                             </EmpDetail>  
  32.                             <EmpDetail>  
  33.                                         <TravelId>2</TravelId><TravelCity>Mumbai</TravelCity>  
  34.                             </EmpDetail>  
  35.                             <EmpDetail>  
  36.                                         <TravelId>3</TravelId><TravelCity>Delhi Region</TravelCity>  
  37.                             </EmpDetail>  
  38.                         </EmpDetails>';  
  39.   
  40.          MERGE #Travel AS S  
  41.              USING (SELECT TravelId = [Table].[Column].value('TravelId[1]''int'),  
  42.                     TravelCity = [Table].[Column].value('TravelCity[1]''varchar(100)')  
  43.              FROM   @EmployeeXML.nodes('/EmpDetails/EmpDetail'AS [Table]([Column])) AS T(TravelId, TravelCity)  
  44.          ON T.TravelId = S.TravelId  
  45.   
  46.          WHEN MATCHED THEN  
  47.                     UPDATE SET S.TravelId = T.TravelId,  
  48.                                S.TravelCity = T.TravelCity                    
  49.          WHEN NOT MATCHED THEN  
  50.                     INSERT (TravelId,   TravelCity)  
  51.                     VALUES (T.TravelId, T.TravelCity);  
  52.   
  53. SELECT * FROM #Travel  
Output when we execute select command as,
 
Data Type in SQL Server 
Summary

In this article, we learned Bulk Insert, Update XML data into SQL Table.