if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee] (
	[EmployeeId] [int] NOT NULL ,
	[FirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE PROC sp_UpdateXML @empdata nText
AS 
 DECLARE @hDoc int   
 exec sp_xml_preparedocument @hDoc OUTPUT,@empdata   

--This code updates old data.
 UPDATE Employee 
 SET 
   Employee.FirstName = XMLEmployee.FirstName,
   Employee.LastName = XMLEmployee.LastName
 FROM OPENXML(@hDoc, 'NewDataSet/Employee')   
       WITH (EmployeeId Integer, FirstName varchar(100),  LastName varchar(100))  XMLEmployee
WHERE    Employee.EmployeeId = XMLEmployee.EmployeeId

--This code inserts new data.

Insert Into Employee 
SELECT   EmployeeId, FirstName, LastName
	FROM       OPENXML (@hdoc, '/NewDataSet/Employee',1)
WITH (EmployeeId Integer, FirstName varchar(100),  LastName varchar(100))  XMLEmployee
Where XMLEmployee.EmployeeId Not IN (Select EmployeeID from Employee)

EXEC sp_xml_removedocument @hDoc
GO

Imports System
Imports System.Data.SqlClient
Imports System.Data

Module Module1

   Sub Main()
      Try
         BulkInsertUpdate()
         System.Console.WriteLine("Successfully inserted and updated data.")
         System.Console.Read()
      Catch e As System.Data.SqlClient.SqlException
         System.Diagnostics.Debug.WriteLine(e.Message)
         System.Console.WriteLine(e.Message)
      End Try
   End Sub
   Sub BulkInsertUpdate()
      ' Steps:
      ' 1. Create the data set.
      ' 2. Update the data set.
      ' 3. Insert some data.
      ' 4. Save the changed data as XML, and then send the XML to 
      ' SQL Server through the stored procedure.

      ' Declaration
      Dim objDS As System.Data.DataSet
      Dim objCon As SqlConnection
      Dim objCom1 As SqlCommand
      Dim objAdpt1 As SqlDataAdapter
      Dim sConn As String
      sConn = "user id=UserName;password=YourPassword;Database=master;Server=ServerName"
      objDS = New DataSet()
      objCon = New SqlConnection(sConn)
      objCon.Open()
      objCom1 = New SqlCommand()
      objCom1.Connection = objCon
      objAdpt1 = New SqlDataAdapter()


      ' Step 1: Create the data set.
      CreateDataSetFromEmployee(objDS, objCom1, objAdpt1)

      ' Step 2: Update the data set.
      Dim tbl As System.Data.DataTable = objDS.Tables("Employee")
      Dim i As Integer = 0
      Dim aRow As DataRow
      For Each aRow In tbl.Rows
         i = i + 1
         aRow("FirstName") = aRow("FirstName").ToString() & i.ToString()
         aRow("LastName") = aRow("LastName").ToString() & i.ToString()
      Next

      ' Step 3: Insert some data.
      Dim ii As Integer
      For ii = 1 To 5 Step ii + 1
         Dim NewRow As DataRow = tbl.NewRow()
         Dim j As Integer = ii + 100
         NewRow("EmployeeId") = j
         NewRow("FirstName") = "Fname" + j.ToString()
         NewRow("LastName") = "LName" + j.ToString()
         tbl.Rows.Add(NewRow)
      Next


      ' 4. Save the changed data as XML, and then send the XML to 
      ' SQL Server through the stored procedure.
      ' In your instance of SQL Server, you have already saved a stored 
      ' procedure that accepts this XML and updates the corresponding table.

      SaveThroughXML(objDS, objCon)
   End Sub

   Sub SaveThroughXML(ByVal objDS As DataSet, ByVal objCon As SqlConnection)
      'Change the column mapping.
      Dim tbl As DataTable = objDS.Tables("Employee")
      Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder(1000)
      Dim sw As System.IO.StringWriter = New System.IO.StringWriter(sb)

      Dim col As DataColumn
      For Each col In tbl.Columns
         col.ColumnMapping = System.Data.MappingType.Attribute
      Next

      objDS.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema)

      Dim objCom As SqlCommand = New SqlCommand()
      objCom.Connection = objCon
      objCom.CommandType = CommandType.StoredProcedure
      objCom.CommandText = "sp_UpdateXML"

      objCom.Parameters.Add(New SqlParameter("@empdata", System.Data.SqlDbType.NText))
      objCom.Parameters(0).Value = sb.ToString()
      objCom.ExecuteNonQuery()
   End Sub

   Sub CreateDataSetFromEmployee(ByVal objDS As DataSet, ByVal objCom1 As SqlCommand, ByVal objAdpt1 As SqlDataAdapter)

      ' Create related objects.
      objCom1.CommandType = CommandType.Text
      objCom1.CommandText = "Select EmployeeId, FirstName, LastName from Employee"

      ' Fill the Orders table.
      objAdpt1.SelectCommand = objCom1
      objAdpt1.TableMappings.Add("Table", "Employee")
      objAdpt1.Fill(objDS)
   End Sub
End Module