Click here to Skip to main content
14,868,583 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi
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:

ASP.NET
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("@Name", bouncesAndBlock.name)
        cmd.Parameters.AddWithValue("@type", bouncesAndBlock.type)
        cmd.Parameters.AddWithValue("@transporter", bouncesAndBlock.transporter)

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

        cmd.Parameters.Clear()
        cmd.Dispose()
    Next

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


ASP.NET
 To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
'<System.Web.Script.Services.ScriptService()>
<WebService(Namespace:="http://tempuri.org/")>
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)>
<System.ComponentModel.ToolboxItem(False)>
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()>
Public Class WebService
    Inherits System.Web.Services.WebService

    <WebMethod>
    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()
        da.Fill(ds)
        Return ds
    End Function
Posted
Updated 5-Apr-21 18:49pm
v2

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