VB - Passing XML string to Bulk Update data in SQL server.

[techyspecy]

This is the script for a table and a stored procedure .. create them as it is in some database on your sql server

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[saveXMLresponse]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[saveXMLresponse]
GO
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Answers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Answers]
GO
 
CREATE TABLE [dbo].[Answers] (
    [EvalID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Qnum] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Answer] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO
 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO
 
CREATE PROC saveXMLresponse
@evaldata varchar(4000) 
AS 
DECLARE @hDoc int 
exec sp_xml_preparedocument @hDoc OUTPUT,@evaldata 
 
--truncate table Answers
INSERT INTO Answers
SELECT *
FROM OPENXML (@hDoc,'/insert/Answers')
WITH Answers
 
 
EXEC sp_xml_removedocument @hDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Option Explicit
 
Const ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDB;Data Source=MyServer"
 
Private Sub Command1_Click()
Dim l_conAdoCon As ADODB.Connection
    
    Set l_conAdoCon = CreateObject("ADODB.Connection")
    
    l_conAdoCon.Open ConnectionString
    
    If l_conAdoCon.State <> adStateOpen Then
        MsgBox "Connection could not be established ...aborting."
        End
    End If
    
Dim l_strXMLString As String
Dim i As Integer
 
    l_strXMLString = "<insert>" & vbCrLf
    
    For i = 1 To 12
        l_strXMLString = l_strXMLString & "<Answers EvalID='" & i & "' Qnum='" & i & "' Answer='" & MonthName(i) & "'/>" & vbCrLf
    Next
    
    l_strXMLString = l_strXMLString & "</insert>" & vbCrLf
        
    l_conAdoCon.SaveXMLResponse l_strXMLString
    
    Set l_conAdoCon = Nothing
End Sub

and use this code to add 12 records to the table using XML and ADO. This is just a small sample of what you can do with XML and SQL.

Remeber to change the connectionstring in the VB code to your server and database where you created the table and SP

Enjoy ....