Passing XML data to stored procedure in SQL Server 2005

I have been working on a project recently that requires to insert a large amount of records from an Oracle database into a SQL Server 2005. I can’t use DTS to transfer the data because the data has to be extensively formatted before being inserted into the SQL Server.

My initial thought was to build a long SQL query by concatenating multiple INSERT statements, then execute the query to have the records to be inserted in a batch mode. However, it turned out not a efficient way because the number of records needs to be inserted is around 50,000 and I need to loop around 50,000 times to build the big string. Also, the data needs to be inserted into two tables with a relationship and I need to make sure the insert operation on the two table is in a transaction, so if anything happens, the database can be rolled back to its original state. My initial plan obviously can’t meet this requirement.

After some search, I found that in SQL Server 2005 I can pass an XML data to a stored procedure where the XML data can be queried for the insert operation.

Anyway, long storey short. First, let’s see how to query the XML data.

1. Query XML data in stored procedure

 SET NOCOUNT ON;
 SET ARITHABORT ON;
 DECLARE @err int
 SET @err = 0
 BEGIN TRAN 
 INSERT INTO customer (customerID, FullName, Department)
 SELECT
 x.d.value(‘CUSTOMERD[1]’,‘CHAR(9)’) AS customerID,
 x.d.value(‘NAME[1]’,‘VARCHAR(32)’) AS FullName,
 x.d.value(‘DEPARTMENT[1]’, ‘VARCHAR(255)’) AS Department
 FROM @data.nodes(‘//DocumentElement/Customer’) x(d)
 SET @err = @@Error
 IF @err <> 0
 BEGIN
 goto ErrorHandler
 END
 INSERT INTO Customer_Group (customerID, groupID)
 SELECT
 x.d.value(‘TECHID[1]’,‘CHAR(9)’) AS customerID,
 x.d.value(‘Group_id[1]’, ‘INT’) AS groupID
 FROM @data.nodes(‘//DocumentElement/Customer’) x(d)
 SET @err = @@Error
 IF @err <> 0
 BEGIN
 goto ErrorHandler
 END 
 COMMIT TRAN

Note: ErrorHandler is just a procedure to roll back the changes in case that anything goes wrong.

To know more about XML data support in SQL Server 2005, check here.

2. Generate XML data on the fly

DataTable has a nice function called WriteXml which can generate a well-formatted XML based on the schema of the DtatTable. Below is the code snippet I used in my code to generate the XML data which is then passed as a parameter to the above stored procedure:

  1: Dim dt As Customer.CustomerDataTable
  2: Dim sw As New StringWriter()
  3:
  4: Try
  5: 	dt = CustomerBLL.GetCustomer()
  6: 	dt.WriteXml(sw, False)
  7: 	Dim xmlData As String = sw.ToString()
  8: 	CustomersBLL.ImportCustomers(xmlData)
  9: Catch ex As Exception
 10: 	SendExceptionEmail(ex)
 11: Finally
 12: 	sw.Close()
 13: 	dt = Nothing
 14: End Try
At line 5, CustomerBLL is a class in my business logic layer and it has several shared (static in C#) functions to manipulate data. For example, ImportCustomer() looks like this:
  1: Public Shared Sub ImportCustomers(ByVal customerXMLData As String)
  2: 	Dim strConn As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
  3: 	Dim conn As SqlConnection = New SqlConnection(strConn)
  4: 	Dim cmd As SqlCommand = New SqlCommand()
  5: 	cmd.Connection = conn
  6: 	cmd.CommandText = "sp_importCurrentCustomers"
  7: 	cmd.CommandType = CommandType.StoredProcedure
  8: 	cmd.Parameters.AddWithValue("@data", customerXMLData)
  9: 	cmd.CommandTimeout = 600
 10: 	Try
 11: 		conn.Open()
 12: 		cmd.ExecuteNonQuery()
 13: 	Catch ex As Exception
 14: 		Throw ex
 15: 	Finally
 16: 		conn.Close()
 17: 	End Try
 18: End Sub
Note: I set CommandTimeOut = 600 (10 minutes) to prevent query execution time-out error because of the large number of records being inserted.