Click here to Skip to main content
14,868,583 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Need advice how to insert xml data into mssql table.
Below code populate xml in browser
Dim url As String = "http://WebService.asmx/GetAllCustomerXML"

Please advice me

Thank you

Below is my code

What I have tried:

Public Class BouncesAndBlocks
    Public Property code As String
    Public Property name As String
    Public Property type As String
    Public Property transporter As String
End Class

    Dim url As String = "http://localhost/delivery/JsonData/WebService.asmx/GetAllCustomer"
    ServicePointManager.Expect100Continue = True
    ServicePointManager.SecurityProtocol = CType(3072, SecurityProtocolType)
    Dim json As String = (New WebClient).DownloadString(url.TrimEnd())
    Dim res As List(Of BouncesAndBlocks) = JsonConvert.DeserializeObject(Of List(Of BouncesAndBlocks))(json)
    For Each bouncesAndBlock In res
        Dim cmd As New SqlCommand
        cmd.Connection = conn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "usp_API_Customer_List"
        cmd.Parameters.Add(New SqlParameter("@Action", "INSERT"))

        cmd.Parameters.AddWithValue("@code", bouncesAndBlock.code)
        cmd.Parameters.AddWithValue("@type", bouncesAndBlock.type)
        cmd.Parameters.AddWithValue("@transporter", bouncesAndBlock.transporter)

        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable


this json code working fine to store into database. But I need xml to store in database as row, cloumn.

 To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
Public Class WebService
    Inherits System.Web.Services.WebService

    Public Function GetAllCustomer() As DataSet
        Dim sql As String = "SELECT code,name,type,transporter FROM AD_Customer_Master order by Name"
        Dim da As SqlDataAdapter = New SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings("ConnectString").ToString())
        Dim ds As DataSet = New DataSet()
        Return ds
    End Function
Updated 5-Apr-21 18:49pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900