Put XML in Database

[ayoungan]

Let's say that I have an array with shipment information.

'Dim sXml As String() = New String(2) {"<shipment><DelDate>11/2/20055:00:00PM</DelDate><TotalFreight>10.00</TotalFreight><CarrierID>UPS</CarrierID><Service>GRD</Service><Tariff>UPSGD</Tariff><CarrierServiceName>UPSGround</CarrierServiceName></shipment>", "<shipment><DelDate>11/2/20055:00:00PM</DelDate><TotalFreight>15.00</TotalFreight><CarrierID>UPS</CarrierID><Service>GRD</Service><Tariff>UPSGD</Tariff><CarrierServiceName>UPSGround</CarrierServiceName></shipment>", "<shipment><DelDate>11/2/20055:00:00PM</DelDate><TotalFreight>22.22</TotalFreight><CarrierID>UPS</CarrierID><Service>2DAY</Service><Tariff>UPSGD</Tariff><CarrierServiceName>UPS2ndDay</CarrierServiceName></shipment>"}


I want to make a function that would put this information in a database with each node as a column.

DelDate
TotalFreight
Service
Tariff
CarrierServiceName

What would be the best way to go about this? The example below is totally wrong in the loop.

Function PutShippingDb(ByVal sXml As String, ByVal Weight As Integer) As Integer
If Weight >= 300 Then
'Do Something
Else
Dim xmlDoc As Xml.XmlDocument = New Xml.XmlDocument
xmlDoc.LoadXml(sXml.ToString())
Dim TotalFreight As String
Dim CarrierServiceName As String
Dim oNodeList As Xml.XmlNodeList
Dim i As Integer = 0
For i = 0 To oNodeList.Count() - 1
Dim sp As SQLStoredProc = New SQLStoredProc
TotalFreight = (oNodeList(i).InnerText.ToString)
CarrierServiceName = (oNodeList(i).InnerText.ToString)
sp.CommandText = "sp_PutShipInfo"
sp.AddParameter("@CartID", cartID, DbType.String, 128, ParameterDirection.Input)
sp.AddParameter("@TotalFreight", TotalFreight, DbType.String, 10, ParameterDirection.Input)
sp.AddParameter("@CarrierServiceName", CarrierServiceName, DbType.String, 100, ParameterDirection.Input)
Dim retval As Integer
retval = Convert.ToInt32(sp.ExecuteValue())
sp.Dispose()
Return retval
Next
End If
End Function