How to connect xml to table SQL Server vb6?

How to connect xml to table SQL Server vb6?

Following are the methods

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

TO CREATE THE CONNECTION WITH SQL SERVER

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

Remember to change the connection string in the VB code to your server and database where you created the table and SP.