Rank: Administration
Groups: Moderators, Administration, Member Joined: 7/16/2009(UTC) Posts: 30   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
|
