Web Service Loop Through XML and Insert into MSSQL Tables

EDN Admin · Nov 23, 2011

Hi
Ive written the below VB that captures the below xml and inserts most of it into a SQL table. 
The <lines> tag however contains multiple <line>âs and if I use the same approach as below I only get one record in my other SQL table. 
From reading around I think I need to build the result into an array and then loop through it. However I canât figure out where to start. Please help? 
Many thanks

XML
<?xml version="1.0" encoding="UTF-8"?> 
<Orders> 
<Order> 
<CustomerAddress> 
<ContactName>Bob Builder</ContactName> 
<AddressLine1>163 Sandhurst Close</AddressLine1> 
<AddressLine2>Stoke Lodge</AddressLine2> 
<AddressLine3>London</AddressLine3> 
<AddressLine4>Over There</AddressLine4> 
<PostCode>BJ69 6AA</PostCode> 
<CountryCode>GB</CountryCode> 
<EmailAddress>x3mc5n5g3tycc5b@here.co.uk</EmailAddress> 
<TelephoneNumber>014566412760</TelephoneNumber> 
</CustomerAddress> 
<DeliveryAddress> 
<ContactName>Bob Builder</ContactName> 
<AddressLine1>163 Sandhurst Close</AddressLine1> 
<AddressLine2>Stoke Lodge</AddressLine2> 
<AddressLine3>London</AddressLine3> 
<AddressLine4>Over There</AddressLine4> 
<PostCode>BJ69 6AA</PostCode> 
<CountryCode>GB</CountryCode> 
</DeliveryAddress> 
<TransactionReference>026-6631795-6534732</TransactionReference> 
<TransactionDate>17/11/2011</TransactionDate> 
<Channel>Web</Channel> 
<Currency>GBP</Currency> 
<VAT>0</VAT> 
<DespatchService>Standard</DespatchService> 
<DespatchCharge>2.8</DespatchCharge> 
<Charges>0</Charges> 
<Discount>0</Discount> 
<SpecialInstructions> 
</SpecialInstructions> 
<Lines> 
<Line> 
<Quantity>1</Quantity> 
<ProductCode>9781402774720</ProductCode> 
<Price>9.99</Price> 
</Line> 
<Line> 
<Quantity>1</Quantity> 
<ProductCode>9781402774720</ProductCode> 
<Price>9.99</Price> 
</Line> 
</Lines> 
<Transactions> 
<Transaction> 
<PaymentMethod>web</PaymentMethod> 
<PaymentTotal>12.79</PaymentTotal> 
</Transaction> 
</Transactions> 
</Order> 
</Orders>

Web Service (VB)
Imports System.Web.Services 
Imports System.Web.Services.Protocols 
Imports System.ComponentModel 
Imports System 
Imports System.Xml 
Imports System.Xml.Serialization 
Imports System.IO 
Imports Microsoft.VisualBasic 
Imports System.Data.SqlClient
<System.Web.Services.WebService(Namespace:=" http://webservice.meemee.me:8620 http://webservice.meemee.me:8620 ")> _ 
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _ 
<ToolboxItem(False)> _ 
Public Class Service1 
Inherits System.Web.Services.WebService
Public Structure Order
Public CA() As CA 
End Structure
 
Public Structure CA
Public CustomerAddress As CustomerAddress 
Public DeliveryAddress As DeliveryAddress 
Public TransactionReference As String 
Public TransactionDate As String 
Public Channel As String 
Public Currency As String 
Public VAT As String 
Public DespatchService As String 
Public DespatchCharge As String 
Public Charges As String 
Public Discount As String 
Public SpecialInstructions As String 
Public Lines As Lines 
Public Transactions As Transactions
 
End Structure 
Public Class CustomerAddress 
Public ContactName As String 
Public AddressLine1 As String 
Public AddressLine2 As String 
Public AddressLine3 As String 
Public AddressLine4 As String 
Public PostCode As String 
Public CountryCode As String 
Public EmailAddress As String 
Public TelephoneNumber As String 
End Class
Public Class DeliveryAddress 
Public ContactName As String 
Public AddressLine1 As String 
Public AddressLine2 As String 
Public AddressLine3 As String 
Public AddressLine4 As String 
Public PostCode As String 
Public CountryCode As String 
End Class 
Public Class Lines 
Public Line As Line
End Class 
Public Class Line 
Public Quantity As String 
Public ProductCode As String 
Public Price As String 
End Class 
Public Class Transactions 
Public Transaction As Transaction 
End Class 
Public Class Transaction 
Public PaymentMethod As String 
Public PaymentTotal As String 
End Class
 
<WebMethod()> _ 
Public Function Orders(Order As CA) As String 
Try 
Dim SQLCon As New SqlClient.SqlConnection 
Dim SQLCmd As New SqlClient.SqlCommand 
SQLCon2.ConnectionString = "Data Source=srver;User ID=####;Password=#####;" 
SQLCon.Open() 
SQLCmd.CommandText = " Insert Into AA_E_Header (CustomerAddress_ContactName,CustomerAddress_AddressLine1,CustomerAddress_AddressLine2,CustomerAddress_AddressLine3,CustomerAddress_AddressLine4,CustomerAddress_PostCode,CustomerAddress_CountryCode,CustomerAddress_EmailAddress,CustomerAddress_TelephoneNumber,DeliveryAddress_ContactName,DeliveryAddress_AddressLine1,DeliveryAddress_AddressLine2,DeliveryAddress_AddressLine3,DeliveryAddress_AddressLine4,DeliveryAddress_PostCode,DeliveryAddress_CountryCode,TransactionReference,TransactionDate,Channel,Currency,VAT,DespatchService,DespatchCharge,Charges,Discount,SpecialInstructions)
" _ 
& " Select @PM_1, @PM_2, @PM_3, @PM_4, @PM_5, @PM_6, @PM_7,
@PM_8, @PM_9, @PM_10, @PM_11, @PM_12, @PM_13, @PM_14, @PM_15, @PM_16, @PM_17, @PM_18, @PM_19, @PM_20, @PM_21, @PM_22, @PM_23, @PM_24,@PM_25,@PM_26" 
Dim PM_1 As New SqlParameter() 
Dim PM_2 As New SqlParameter() 
Dim PM_3 As New SqlParameter() 
Dim PM_4 As New SqlParameter() 
Dim PM_5 As New SqlParameter() 
Dim PM_6 As New SqlParameter() 
Dim PM_7 As New SqlParameter() 
Dim PM_8 As New SqlParameter() 
Dim PM_9 As New SqlParameter() 
Dim PM_10 As New SqlParameter() 
Dim PM_11 As New SqlParameter() 
Dim PM_12 As New SqlParameter() 
Dim PM_13 As New SqlParameter() 
Dim PM_14 As New SqlParameter() 
Dim PM_15 As New SqlParameter() 
Dim PM_16 As New SqlParameter() 
Dim PM_17 As New SqlParameter() 
Dim PM_18 As New SqlParameter() 
Dim PM_19 As New SqlParameter() 
Dim PM_20 As New SqlParameter() 
Dim PM_21 As New SqlParameter() 
Dim PM_22 As New SqlParameter() 
Dim PM_23 As New SqlParameter() 
Dim PM_24 As New SqlParameter() 
Dim PM_25 As New SqlParameter() 
Dim PM_26 As New SqlParameter()
PM_1.ParameterName = "@PM_1" 
PM_2.ParameterName = "@PM_2" 
PM_3.ParameterName = "@PM_3" 
PM_4.ParameterName = "@PM_4" 
PM_5.ParameterName = "@PM_5" 
PM_6.ParameterName = "@PM_6" 
PM_7.ParameterName = "@PM_7" 
PM_8.ParameterName = "@PM_8" 
PM_9.ParameterName = "@PM_9" 
PM_10.ParameterName = "@PM_10" 
PM_11.ParameterName = "@PM_11" 
PM_12.ParameterName = "@PM_12" 
PM_13.ParameterName = "@PM_13" 
PM_14.ParameterName = "@PM_14" 
PM_15.ParameterName = "@PM_15" 
PM_16.ParameterName = "@PM_16" 
PM_17.ParameterName = "@PM_17" 
PM_18.ParameterName = "@PM_18" 
PM_19.ParameterName = "@PM_19" 
PM_20.ParameterName = "@PM_20" 
PM_21.ParameterName = "@PM_21" 
PM_22.ParameterName = "@PM_22" 
PM_23.ParameterName = "@PM_23" 
PM_24.ParameterName = "@PM_24" 
PM_25.ParameterName = "@PM_25" 
PM_26.ParameterName = "@PM_26"
PM_1.Value = Order.CustomerAddress.ContactName 
PM_2.Value = Order.CustomerAddress.AddressLine1 
PM_3.Value = Order.CustomerAddress.AddressLine2 
PM_4.Value = Order.CustomerAddress.AddressLine3 
PM_5.Value = Order.CustomerAddress.AddressLine4 
PM_6.Value = Order.CustomerAddress.PostCode 
PM_7.Value = Order.CustomerAddress.CountryCode 
PM_8.Value = Order.CustomerAddress.EmailAddress 
PM_9.Value = Order.CustomerAddress.TelephoneNumber 
PM_10.Value = Order.DeliveryAddress.ContactName 
PM_11.Value = Order.DeliveryAddress.AddressLine1 
PM_12.Value = Order.DeliveryAddress.AddressLine2 
PM_13.Value = Order.DeliveryAddress.AddressLine3 
PM_14.Value = Order.DeliveryAddress.AddressLine4 
PM_15.Value = Order.DeliveryAddress.PostCode 
PM_16.Value = Order.DeliveryAddress.CountryCode 
PM_17.Value = Order.TransactionReference 
PM_18.Value = Order.TransactionDate 
PM_19.Value = Order.Channel 
PM_20.Value = Order.Currency 
PM_21.Value = Order.VAT 
PM_22.Value = Order.DespatchService 
PM_23.Value = Order.DespatchCharge 
PM_24.Value = Order.Charges 
PM_25.Value = Order.Discount 
PM_26.Value = Order.SpecialInstructions

SQLCmd.Parameters.Add(PM_1) 
SQLCmd.Parameters.Add(PM_2) 
SQLCmd.Parameters.Add(PM_3) 
SQLCmd.Parameters.Add(PM_4) 
SQLCmd.Parameters.Add(PM_5) 
SQLCmd.Parameters.Add(PM_6) 
SQLCmd.Parameters.Add(PM_7) 
SQLCmd.Parameters.Add(PM_8) 
SQLCmd.Parameters.Add(PM_9) 
SQLCmd.Parameters.Add(PM_10) 
SQLCmd.Parameters.Add(PM_11) 
SQLCmd.Parameters.Add(PM_12) 
SQLCmd.Parameters.Add(PM_13) 
SQLCmd.Parameters.Add(PM_14) 
SQLCmd.Parameters.Add(PM_15) 
SQLCmd.Parameters.Add(PM_16) 
SQLCmd.Parameters.Add(PM_17) 
SQLCmd.Parameters.Add(PM_18) 
SQLCmd.Parameters.Add(PM_19) 
SQLCmd.Parameters.Add(PM_20) 
SQLCmd.Parameters.Add(PM_21) 
SQLCmd.Parameters.Add(PM_22) 
SQLCmd.Parameters.Add(PM_23) 
SQLCmd.Parameters.Add(PM_24) 
SQLCmd.Parameters.Add(PM_25) 
SQLCmd.Parameters.Add(PM_26) 
SQLCmd.Connection = SQLCon 
SQLCmd.ExecuteNonQuery() 
SQLCmd.Connection.Close() 
SQLCmd.Parameters.Clear() 
SQLCon.Close()

 
Dim SQLCon2 As New SqlClient.SqlConnection 
Dim SQLCmd2 As New SqlClient.SqlCommand 
SQLCon2.ConnectionString = "Data Source=srver;User ID=####;Password=#####;" 
SQLCon2.Open() 
SQLCmd2.CommandText = "Insert Into AA_E_Details ( Product_Code,Price , Quantity) Select @PM_Lines_1, @PM_Lines_2,@PM_Lines_3 "
Dim PM_Lines_1 As New SqlParameter() 
Dim PM_Lines_2 As New SqlParameter() 
Dim PM_Lines_3 As New SqlParameter()
PM_Lines_1.ParameterName = "@PM_Lines_1" 
PM_Lines_2.ParameterName = "@PM_Lines_2" 
PM_Lines_3.ParameterName = "@PM_Lines_3"
PM_Lines_1.Value = Order.Lines.Line.ProductCode 
PM_Lines_2.Value = Order.Lines.Line.Price 
PM_Lines_3.Value = Order.Lines.Line.Quantity
SQLCmd2.Parameters.Add(PM_Lines_1) 
SQLCmd2.Parameters.Add(PM_Lines_2) 
SQLCmd2.Parameters.Add(PM_Lines_3)
SQLCmd2.Connection = SQLCon2 
SQLCmd2.ExecuteNonQuery() 
SQLCmd2.Connection.Close() 
SQLCmd2.Parameters.Clear() 
SQLCon2.Close()

Return "Order " & Order.TransactionReference & " Accepted"

Catch ex As Exception 
Return "Error: " + ex.ToString 
End Try
End Function

 
End Class

View the full article

Web Service Loop Through XML and Insert into MSSQL Tables

EDN Admin

Well-known member

Similar threads