logo
Welcome Guest! To enable all features please Login. New Registrations are disabled.

Notification

Icon
Error

Options
Go to last post Go to first unread
Joey  
#1 Posted : Monday, November 9, 2009 1:30:35 PM(UTC)
Joey

Rank: Administration

Reputation:

Groups: Moderators, Administration, Member
Joined: 7/16/2009(UTC)
Posts: 30
Man
Netherlands
Location: Groesbeek

Thanks: 1 times
If you have a xml file which needs to be imported and stored in the database XPath navigation can be very useful. In the example given in this topic a xml file is imported and stored in an array.

After the import several variables are declared which will be filled with data from the xml file. Navigation through the xml file takes place with so called XPath expressions. When all items are found and placed in the array a loop is initiated for all the items which were found.

Another query is launched to get the productId's of the items currently in the database. When a product already exists an updatequery is created, if a product is new then an insertquery is created.

At the end a query is executed to update or insert products.

Code:

Imports System.Xml.XPath
Imports Microsoft.VisualBasic
==>END IMPORTS

Dim Cid As String = pckcid

Dim nav As XPathNavigator
Dim docNav As XPathDocument

' Open the XML.
docNav = New XPathDocument("" & PckRoot & "\xml\" & PckNowYear & "" & PckNowMonth & "" & PckNowDay & "ITEMS.xml")

' Create a navigator to query with XPath.
nav = docNav.CreateNavigator()

' Initial XPathNavigator to start at the root.
nav.MoveToRoot()

Dim nodeItor As XPathNodeIterator = nav.Select("/eExact/Items/Item")
Dim ProductItems(nodeItor.Count - 1, 12) As String

While (nodeItor.MoveNext())

  ' Datavariables
  Dim Code As String = ""
  Dim Searchcode As String = ""
  Dim Description As String = ""
  Dim LongDescription As String = ""
  Dim MultiDescription As String = ""
  Dim AssortmentNumber As String = ""
  Dim PriceValue As String = ""
  Dim ImageName As String = ""
  Dim FreeText As String = ""
  Dim ExtraText As String = ""
  Dim ItemCategory3Description As String = ""
  Dim ItemCategory8Description As String = ""
  Dim ItemCategory9Description As String = ""

  Code = nodeItor.Current.GetAttribute("code", "").Trim()
  Searchcode = nodeItor.Current.GetAttribute("searchcode", "").Trim()
  Description = nodeItor.Current.SelectSingleNode("./Description").Value.Trim()

  If Not nodeItor.Current.SelectSingleNode("./LongDescription") Is Nothing Then
    LongDescription = nodeItor.Current.SelectSingleNode("./LongDescription").Value.Trim()
  End If

  If Not nodeItor.Current.SelectSingleNode("./MultiDescriptions/MultiDescription[@number='1']") Is Nothing Then
    MultiDescription = nodeItor.Current.SelectSingleNode("./MultiDescriptions/MultiDescription[@number='1']").Value.Trim()
  End If

  If Not nodeItor.Current.SelectSingleNode("./Assortment[0]") Is Nothing AndAlso Not nodeItor.Current.SelectSingleNode("./Assortment[0]").GetAttribute("number", "") Is Nothing Then
    AssortmentNumber = nodeItor.Current.SelectSingleNode("./Assortment[0]").GetAttribute("number", "").Trim()
  End If

  If Not nodeItor.Current.SelectSingleNode("./Sales/Price/Value") Is Nothing Then
    PriceValue = nodeItor.Current.SelectSingleNode("./Sales/Price/Value").Value.Trim()
  End If

  If Not nodeItor.Current.SelectSingleNode("./Image/Name") Is Nothing Then
    ImageName = nodeItor.Current.SelectSingleNode("./Image/Name").Value.Trim()
  End If

  If Not nodeItor.Current.SelectSingleNode("./FreeFields/FreeTexts/FreeText[@number='1']") Is Nothing Then
    FreeText = nodeItor.Current.SelectSingleNode("./FreeFields/FreeTexts/FreeText[@number='1']").Value.Trim()
  End If

  If Not nodeItor.Current.SelectSingleNode("./ItemTexts/ItemText/ExtraText[@code='GB']/Description") Is Nothing Then
    ExtraText = nodeItor.Current.SelectSingleNode("./ItemTexts/ItemText/ExtraText[@code='GB']/Description").Value.Trim()
  End If

  If Not nodeItor.Current.SelectSingleNode("./ItemCategory[@number='3']/Description") Is Nothing Then
    ItemCategory3Description = nodeItor.Current.SelectSingleNode("./ItemCategory[@number='3']/Description").Value.Trim()
  End If

  If Not nodeItor.Current.SelectSingleNode("./ItemCategory[@number='8']/Description") Is Nothing Then
    ItemCategory8Description = nodeItor.Current.SelectSingleNode("./ItemCategory[@number='8']/Description").Value.Trim()
  End If

  If Not nodeItor.Current.SelectSingleNode("./ItemCategory[@number='9']/Description") Is Nothing Then
    ItemCategory9Description = nodeItor.Current.SelectSingleNode("./ItemCategory[@number='9']/Description").Value.Trim()
  End If

  Dim i As Integer = nodeItor.CurrentPosition - 1
  ProductItems(i, 0) = Code.Replace("'","''")
  ProductItems(i, 1) = Searchcode.Replace("'","''")
  ProductItems(i, 2) = Description.Replace("'","''")
  ProductItems(i, 3) = LongDescription.Replace("'","''")
  ProductItems(i, 4) = MultiDescription.Replace("'","''")
  ProductItems(i, 5) = AssortmentNumber.Replace("'","''")
  ProductItems(i, 6) = PriceValue.Replace("'","''")
  ProductItems(i, 7) = ImageName.Replace("'","''")
  ProductItems(i, 8) = FreeText.Replace("'","''")
  ProductItems(i, 9) = ExtraText.Replace("'","''")
  ProductItems(i, 10) = ItemCategory3Description.Replace("'","''")
  ProductItems(i, 11) = ItemCategory8Description.Replace("'","''")
  ProductItems(i, 12) = ItemCategory9Description.Replace("'","''")

End While

' Get all productnumbers.
Dim ds As New DataSet
Using con As New SqlConnection(Constring)
  Dim qry As String = "SELECT Artikelcode FROM " & Cid & "U_Artikelen"
  Dim adt As New SqlDataAdapter(qry, con)
  adt.Fill(ds)
End Using

Dim Query As String = ""

' Loop through all imported products. If a match is found based on 'artikelcode'
' then an update query is executed, else the new product is inserted.
For i As Integer = 0 To ProductItems.GetLength(0) - 1
  Dim Exists As Boolean = False
  
  For Each row As DataRow In ds.Tables(0).Rows
    If ProductItems(i,0) = row(0) Then ' Product already exists, change boolean to true.
      Exists = True
    End If
  Next

  If Exists Then ' Update product query.
    Query &= " " & VBCRLF & _
             "UPDATE " & Cid & "U_Artikelen" & VBCRLF & _
             "SET Zoekcode = '" & ProductItems(i, 1) & "'" & VBCRLF & _
             ",Omschrijving = '" & ProductItems(i, 2) & "'" & VBCRLF & _
             ",Omschrijvinglang = '" & ProductItems(i, 3) & "'" & VBCRLF & _
             ",OmgschrijvingENG = '" & ProductItems(i, 4) & "'" & VBCRLF & _
             ",Artikelgroep = '" & ProductItems(i, 5) & "'" & VBCRLF & _
             ",Prijs = '" & ProductItems(i, 6) & "'" & VBCRLF & _
             ",Foto = '" & ProductItems(i, 7) & "'" & VBCRLF & _
             ",GerelateerdeArtikelen = '" & ProductItems(i, 8) & "'" & VBCRLF & _
             ",OmschrijvinglangEN = '" & ProductItems(i, 9) & "'" & VBCRLF & _
             ",LEDInternet1 = '" & ProductItems(i, 10) & "'" & VBCRLF & _
             ",Prijslijst = '" & ProductItems(i, 11) & "'" & VBCRLF & _
             ",Rentall = '" & ProductItems(i, 12) & "'" & VBCRLF & _
             "WHERE Artikelcode = '" & ProductItems(i, 0) & "'" & VBCRLF & _
             " " & VBCRLF
  Else ' Insert product query.
    Query &= " " & VBCRLF & _ 
             "INSERT INTO " & Cid & "U_Artikelen " & VBCRLF & _
             "(" & VBCRLF & _
             "Artikelcode" & VBCRLF & _
             ",Zoekcode" & VBCRLF & _
             ",Omschrijving" & VBCRLF & _
             ",Omschrijvinglang" & VBCRLF & _
             ",OmgschrijvingENG" & VBCRLF & _
             ",Artikelgroep" & VBCRLF & _
             ",Prijs" & VBCRLF & _
             ",Foto" & VBCRLF & _
             ",GerelateerdeArtikelen" & VBCRLF & _
             ",OmschrijvinglangEN" & VBCRLF & _
             ",LEDInternet1" & VBCRLF & _
             ",Prijslijst" & VBCRLF & _
             ",Rentall" & VBCRLF & _
             ") " & VBCRLF & _
             "VALUES " & VBCRLF & _
             "(" & VBCRLF & _
             "'" & ProductItems(i, 0) & "'," & VBCRLF & _
             "'" & ProductItems(i, 1) & "'," & VBCRLF & _
             "'" & ProductItems(i, 2) & "'," & VBCRLF & _
             "'" & ProductItems(i, 3) & "'," & VBCRLF & _
             "'" & ProductItems(i, 4) & "'," & VBCRLF & _
             "'" & ProductItems(i, 5) & "'," & VBCRLF & _
             "'" & ProductItems(i, 6) & "'," & VBCRLF & _
             "'" & ProductItems(i, 7) & "'," & VBCRLF & _
             "'" & ProductItems(i, 8) & "'," & VBCRLF & _
             "'" & ProductItems(i, 9) & "'," & VBCRLF & _
             "'" & ProductItems(i, 10) & "'," & VBCRLF & _
             "'" & ProductItems(i, 11) & "'," & VBCRLF & _
             "'" & ProductItems(i, 12) & "'" & VBCRLF & _
             ")" & VBCRLF & _
             " " & VBCRLF
  End If
Next 

Using Con As New SqlConnection(Constring) 
  Dim Cmd As New SqlCommand(Query, Con)
  Try
    Con.Open()
    Cmd.ExecuteNonQuery()
    
  Catch
  Finally
    Con.Close()
  End Try
End Using

Edited by user Tuesday, November 10, 2009 5:24:38 PM(UTC)  | Reason: Not specified

Wanna join the discussion?! Login to your Sienn Forum forum account. New Registrations are disabled.

Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.