[KennethGarza]

Hello everyone,

I have a question on how VB.NET parses XML data. I currently have code that IS working to pull data from an XML document and load it into a MySQL database. All is working but the problem is that the time it takes to parse the data seems to take a long time. The file itself is quite large weighing in at around 2.5 MB/document. I have had up until this point very little to do with XML.

Here are a few of the details, the XML data file contains no name space schema or any type of internal identifiers for the document. I do have a xml format chart that was given to me by the maker of the product that is doing the xml feed. The XML feed is is zero suppressed so any value that is empty or zeroed is not included in the document.

For some reason this device has a few items that are read as invalid characters by the vb xml reader so I have to import the file as a string into the program, remove any invalid characters from the string, and then pump that data into the xml reader.

I am not a fan of how I wrote this code so please be forgiving on the formatting, I am still in "test" mode so once I get it working the way I want then I will break it down into subs and functions to clean it up a bit.

Please remember that this code IS working the way I want to, it just seems to take a while and was hoping for a more efficient way.

Thanks in advance

Imports System.IO
Imports System.Xml

Module mod_focusxml

    Public c = "','"
    Public cc = ","

    Public Sub GetXML()
        On Error Resume Next

        Dim xmlDoc As New XmlDocument
        Dim xmlFile As String = ""

        Dim sqlCollection As New Collection

        Dim ofd As New OpenFileDialog
        ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        ofd.ShowDialog()
        xmlFile = ofd.FileName

        If xmlFile = "" Then Exit Sub

        Dim s = Split(RemovePath(xmlFile), "-")
        Dim StoreID As String = s(1).ToString
        Dim BusinessDate As String = s(0)(0).ToString + s(0)(1).ToString + "/" + _
            s(0)(2).ToString + s(0)(3).ToString + "/" + _
            s(0)(4).ToString + s(0)(5).ToString + s(0)(6).ToString + s(0)(7).ToString

        Dim XMLstring = readfromfilestr(xmlFile)
        XMLstring = Replace(XMLstring, "ÿ", "")

        xmlDoc.LoadXml(XMLstring)

        doSQL("delete from CheckRecord where BusinessDate='" + BusinessDate + "' and storeid='" + StoreID + "'")
        doSQL("delete from SeatRecord where BusinessDate='" + BusinessDate + "' and storeid='" + StoreID + "'")
        doSQL("delete from CheckItemRecord where BusinessDate='" + BusinessDate + "' and storeid='" + StoreID + "'")

        Dim aa As Integer = 0

        For Each n1 In xmlDoc.GetElementsByTagName("Check")

            aa += 1

            Dim Check As XmlElement = n1

            Dim CheckID As String = ""

            For Each n2 In Check.GetElementsByTagName("CheckRecord")
                Dim CheckRecord As XmlElement = n2

                Dim NumberofSeats, FlagsUsed, FlagsOpen, FlagsTabNameEntered, FlagsReopened, FlagsTransferred As New String("")
                Dim FlagsRenumbered, FlagsMoved, FlagsRefunded, FlagsBeveragesOrdered, FlagsAppetizersOrdered As New String("")
                Dim FlagsEntreesOrdered, FlagsDessertsOrdered, TimeOpened, OpenerID, OpenerName, OwnerID As New String("")
                Dim OwnerName, TimeClosed, CloserID, CloserName, StationClosedID, TimeReopened, TimeHold, Table As New String("")
                Dim Guests, OrderTypeID, RevenueCenterID, StationOpenedID, OwnerTimeCardID, TransfererID As New String("")
                Dim TransfererName, TransferTime, AllSeatsVoided As New String("")
                Dim Total, VoidTotalAmount, TaxableSales, TaxExemptSales, OTChargeItemDiscount, GiftCardItemDiscount As New String("0.00")

                CheckID = CheckRecord.GetElementsByTagName("ID")(0).InnerText.ToString
                NumberOfSeats = CheckRecord.GetElementsByTagName("NumberOfSeats")(0).InnerText.ToString
                FlagsUsed = CheckRecord.GetElementsByTagName("FlagsUsed")(0).InnerText.ToString
                FlagsOpen = CheckRecord.GetElementsByTagName("FlagsOpen")(0).InnerText.ToString
                FlagsTabNameEntered = CheckRecord.GetElementsByTagName("FlagsTabNameEntered")(0).InnerText.ToString
                FlagsReopened = CheckRecord.GetElementsByTagName("FlagsReopened")(0).InnerText.ToString
                FlagsTransferred = CheckRecord.GetElementsByTagName("FlagsTransferred")(0).InnerText.ToString
                FlagsRenumbered = CheckRecord.GetElementsByTagName("FlagsRenumbered")(0).InnerText.ToString
                FlagsMoved = CheckRecord.GetElementsByTagName("FlagsMoved")(0).InnerText.ToString
                FlagsRefunded = CheckRecord.GetElementsByTagName("FlagsRefunded")(0).InnerText.ToString
                FlagsBeveragesOrdered = CheckRecord.GetElementsByTagName("FlagsBEveragesOrdered")(0).InnerText.ToString
                FlagsAppetizersOrdered = CheckRecord.GetElementsByTagName("FlagsAppetizersOrdered")(0).InnerText.ToString
                FlagsEntreesOrdered = CheckRecord.GetElementsByTagName("FlagsEntressOrdered")(0).InnerText.ToString
                FlagsDessertsOrdered = CheckRecord.GetElementsByTagName("FlagsDessertsOrdered")(0).InnerText.ToString
                TimeOpened = CheckRecord.GetElementsByTagName("TimeOpened")(0).InnerText.ToString
                OpenerID = CheckRecord.GetElementsByTagName("OpenerID")(0).InnerText.ToString
                OpenerName = CheckRecord.GetElementsByTagName("OpenerName")(0).InnerText.ToString
                OwnerID = CheckRecord.GetElementsByTagName("OwnerID")(0).InnerText.ToString
                OwnerName = CheckRecord.GetElementsByTagName("OwnerName")(0).InnerText.ToString
                TimeClosed = CheckRecord.GetElementsByTagName("TimeClosed")(0).InnerText.ToString
                CloserID = CheckRecord.GetElementsByTagName("CloserID")(0).InnerText.ToString
                CloserName = CheckRecord.GetElementsByTagName("CloserName")(0).InnerText.ToString
                StationClosedID = CheckRecord.GetElementsByTagName("StationClosedID")(0).InnerText.ToString
                TimeReopened = CheckRecord.GetElementsByTagName("TimeReopened")(0).InnerText.ToString
                TimeHold = CheckRecord.GetElementsByTagName("TimeHold")(0).InnerText.ToString
                Table = CheckRecord.GetElementsByTagName("Table")(0).InnerText.ToString
                Guests = CheckRecord.GetElementsByTagName("Guests")(0).InnerText.ToString
                OrderTypeID = CheckRecord.GetElementsByTagName("OrderTypeID")(0).InnerText.ToString
                RevenueCenterID = CheckRecord.GetElementsByTagName("RevenueCenterID")(0).InnerText.ToString
                StationOpenedID = CheckRecord.GetElementsByTagName("StationOpenedID")(0).InnerText.ToString
                OwnerTimeCardID = CheckRecord.GetElementsByTagName("OwnerTimeCardID")(0).InnerText.ToString
                TransfererID = CheckRecord.GetElementsByTagName("TransfererID")(0).InnerText.ToString
                TransfererName = CheckRecord.GetElementsByTagName("TransferName")(0).InnerText.ToString
                TransferTime = CheckRecord.GetElementsByTagName("TransferTime")(0).InnerText.ToString
                AllSeatsVoided = CheckRecord.GetElementsByTagName("AllSeatsVoided")(0).InnerText.ToString
                Total = CheckRecord.GetElementsByTagName("Total")(0).InnerText.ToString
                VoidTotalAmount = CheckRecord.GetElementsByTagName("VoidTotalAmount")(0).InnerText.ToString
                TaxableSales = CheckRecord.GetElementsByTagName("TaxableSales(1-16)")(0).InnerText.ToString
                TaxExemptSales = CheckRecord.GetElementsByTagName("TaxExemptSales(1-16)")(0).InnerText.ToString
                OTChargeItemDiscount = CheckRecord.GetElementsByTagName("OTChargeItemDiscount")(0).InnerText.ToString
                GiftCardItemDiscount = CheckRecord.GetElementsByTagName("GiftCardItemDiscount")(0).InnerText.ToString

                Dim sql = "insert into CheckRecord values ('" + _
                        StoreID + c + CheckID + c + NumberofSeats + c + FlagsUsed + c + FlagsOpen + c + _
                        FlagsTabNameEntered + c + FlagsReopened + c + FlagsTransferred + c + _
                        FlagsRenumbered + c + FlagsMoved + c + FlagsRefunded + c + FlagsBeveragesOrdered + c + _
                        FlagsAppetizersOrdered + c + FlagsEntreesOrdered + c + FlagsDessertsOrdered + c + _
                        TimeOpened + c + OpenerID + c + OpenerName + c + OwnerID + c + OwnerName + c + _
                        TimeClosed + c + CloserID + c + CloserName + c + StationClosedID + c + TimeReopened + c + _
                        TimeHold + c + Table + c + Guests + c + OrderTypeID + c + RevenueCenterID + c + _
                        StationOpenedID + c + OwnerTimeCardID + c + TransfererID + c + TransfererName + c + _
                        AllSeatsVoided + "'," + Total + cc + VoidTotalAmount + cc + TaxableSales + cc + _
                        TaxExemptSales + cc + OTChargeItemDiscount + cc + GiftCardItemDiscount + ",'" + BusinessDate + "')"

                sql = Replace(sql, "$", "")
                sqlCollection.Add(sql)
            Next

            For Each n2 In Check.GetElementsByTagName("SeatRecord")
                Dim SeatRecord As XmlElement = n2

                Dim CheckInternal, SeatNumber, Key, FlagsUsed, FlagsOpen, TimesPrinted As New String("")
                Dim SubTotal, ItemDiscountTotal, SubtotalDiscountTotal, OrderTypeChargeTotal As New String("0.00")
                Dim GratuityTotal, AdvanceTotal, GiftCardTotal, ChangeBack, Total, TaxTotal As New String("0.00")

                CheckInternal = SeatRecord.GetElementsByTagName("CheckInternal")(0).InnerText.ToString
                SeatNumber = SeatRecord.GetElementsByTagName("SeatNumber")(0).InnerText.ToString
                Key = SeatRecord.GetElementsByTagName("Key")(0).InnerText.ToString
                FlagsUsed = SeatRecord.GetElementsByTagName("FlagsUsed")(0).InnerText.ToString
                FlagsOpen = SeatRecord.GetElementsByTagName("FlagsOpen")(0).InnerText.ToString
                TimesPrinted = SeatRecord.GetElementsByTagName("TimesPrinted")(0).InnerText.ToString
                Subtotal = SeatRecord.GetElementsByTagName("Subtotal")(0).InnerText.ToString
                ItemDiscountTotal = SeatRecord.GetElementsByTagName("ItemDiscountTotal")(0).InnerText.ToString
                SubtotalDiscountTotal = SeatRecord.GetElementsByTagName("SubtotalDiscountTotal")(0).InnerText.ToString
                OrderTypeChargeTotal = SeatRecord.GetElementsByTagName("OrderTypeChargeTotal")(0).InnerText.ToString
                GratuityTotal = SeatRecord.GetElementsByTagName("GratuityTotal")(0).InnerText.ToString
                AdvanceTotal = SeatRecord.GetElementsByTagName("AdvanceTotal")(0).InnerText.ToString
                GiftCardTotal = SeatRecord.GetElementsByTagName("GiftCardTotal")(0).InnerText.ToString
                ChangeBack = SeatRecord.GetElementsByTagName("ChangeBack")(0).InnerText.ToString
                Total = SeatRecord.GetElementsByTagName("Total")(0).InnerText.ToString
                TaxTotal = SeatRecord.GetElementsByTagName("TaxTotal(1-16)")(0).InnerText.ToString

                Dim sql = "insert into SeatRecord values ('" + _
                        StoreID + c + BusinessDate + c + CheckInternal + c + SeatNumber + c + Key + c + FlagsUsed + c + FlagsOpen + c + _
                        TimesPrinted + "'," + SubTotal + cc + ItemDiscountTotal + cc + SubtotalDiscountTotal + cc + OrderTypeChargeTotal + cc + _
                        GratuityTotal + cc + AdvanceTotal + cc + GiftCardTotal + cc + ChangeBack + cc + Total + cc + TaxTotal + ",'" + CheckID + "')"

                sql = Replace(sql, "$", "")
                sqlCollection.Add(sql)
            Next

            For Each n2 In Check.GetElementsByTagName("CheckItemRecord")
                Dim CheckItemRecord As XmlElement = n2

                Dim SeatKey, Key, ItemKey, Position, RecordNumber, ID, GuestCheckName, ReportGroupID As New String("")
                Dim SortKey, StationID, OwnerTimeCardID, OwnerID, OwnerName, TimeStamp, PriceNumber As New String("")
                Dim Qty, FlagsVoid, FlagsDiscounted, FlagsSub, FlagsExtra, FlagsNo As New String("")
                Dim Price, Extenstion As New String("0.00")
                Dim FlagsIncudeInprice, FlagsPrintModifier, FlagsKitchenComment, FlagsDiscountRequirement As New String("")
                Dim FlagsTimedRate, FlagsReturnItem, Level, PriceNumberName, VoidID, VoidEmployeeName As New String("")
                Dim TimeVoided, HoldTime As New String("")
                Dim OrderTypeAmount, DiscountAmount As New String("0.00")
                Dim DiscountInternalID As New String("")

                SeatKey = CheckItemRecord.GetElementsByTagName("SeatKey")(0).ToString
                Key = CheckItemRecord.GetElementsByTagName("Key")(0).ToString
                ItemKey = CheckItemRecord.GetElementsByTagName("ItemKey")(0).ToString
                Position = CheckItemRecord.GetElementsByTagName("Position")(0).ToString
                RecordNumber = CheckItemRecord.GetElementsByTagName("RecordNumber")(0).ToString
                ID = CheckItemRecord.GetElementsByTagName("ID")(0).ToString
                GuestCheckName = CheckItemRecord.GetElementsByTagName("GuestCheckName")(0).ToString
                ReportGroupID = CheckItemRecord.GetElementsByTagName("ReportGroupID")(0).ToString
                SortKey = CheckItemRecord.GetElementsByTagName("SortKey")(0).ToString
                StationID = CheckItemRecord.GetElementsByTagName("StationID")(0).ToString
                OwnerTimeCardID = CheckItemRecord.GetElementsByTagName("OwnerTimeCardID")(0).ToString
                OwnerID = CheckItemRecord.GetElementsByTagName("OwnerID")(0).ToString
                OwnerName = CheckItemRecord.GetElementsByTagName("OwnerName")(0).ToString
                TimeStamp = CheckItemRecord.GetElementsByTagName("TimeStamp")(0).ToString
                PriceNumber = CheckItemRecord.GetElementsByTagName("PriceNumber")(0).ToString
                Qty = CheckItemRecord.GetElementsByTagName("Qty")(0).ToString
                Price = CheckItemRecord.GetElementsByTagName("Price")(0).ToString
                Extenstion = CheckItemRecord.GetElementsByTagName("Extenstion")(0).ToString
                FlagsVoid = CheckItemRecord.GetElementsByTagName("FlagsVoid")(0).ToString
                FlagsDiscounted = CheckItemRecord.GetElementsByTagName("FlagsDiscounted")(0).ToString
                FlagsSub = CheckItemRecord.GetElementsByTagName("FlagsSub")(0).ToString
                FlagsExtra = CheckItemRecord.GetElementsByTagName("FlagsExtra")(0).ToString
                FlagsNo = CheckItemRecord.GetElementsByTagName("FlagsNo")(0).ToString
                FlagsIncudeInprice = CheckItemRecord.GetElementsByTagName("FlagsIncludeInPrice")(0).ToString
                FlagsPrintModifier = CheckItemRecord.GetElementsByTagName("FlagsPrintModifier")(0).ToString
                FlagsKitchenComment = CheckItemRecord.GetElementsByTagName("FlagsKitchenComment")(0).ToString
                FlagsDiscountRequirement = CheckItemRecord.GetElementsByTagName("FlagsDiscountRequirement")(0).ToString
                FlagsTimedRate = CheckItemRecord.GetElementsByTagName("FlagsTimedRate")(0).ToString
                FlagsReturnItem = CheckItemRecord.GetElementsByTagName("FlagsReturnItem")(0).ToString
                Level = CheckItemRecord.GetElementsByTagName("Level")(0).ToString
                PriceNumberName = CheckItemRecord.GetElementsByTagName("PriceNumberName")(0).ToString
                VoidID = CheckItemRecord.GetElementsByTagName("VoidID")(0).ToString
                VoidEmployeeName = CheckItemRecord.GetElementsByTagName("VoidEmployeeName")(0).ToString
                TimeVoided = CheckItemRecord.GetElementsByTagName("TimeVoided")(0).ToString
                HoldTime = CheckItemRecord.GetElementsByTagName("HoldTime")(0).ToString
                OrderTypeAmount = CheckItemRecord.GetElementsByTagName("OrderTypeAmount")(0).ToString
                DiscountAmount = CheckItemRecord.GetElementsByTagName("DiscountAmount")(0).ToString
                DiscountInternalID = CheckItemRecord.GetElementsByTagName("DiscountInternalID")(0).ToString


                Dim sql = "insert into SeatRecord values ('" + _
                        StoreID + c + BusinessDate + c + CheckID + c + SeatKey + c + Key + c + ItemKey + c + Position + c + RecordNumber + c + _
                        ID + c + GuestCheckName + c + ReportGroupID + c + SortKey + c + StationID + c + OwnerTimeCardID + c + OwnerID + c + _
                        OwnerName + c + TimeStamp + c + PriceNumber + c + Qty + "'," + Price + cc + Extenstion + ",'" + FlagsVoid + c + _
                        FlagsDiscounted + c + FlagsSub + c + FlagsExtra + c + FlagsNo + c + FlagsIncudeInprice + c + FlagsPrintModifier + c + _
                        FlagsKitchenComment + c + FlagsDiscountRequirement + c + FlagsTimedRate + c + FlagsReturnItem + c + Level + c + _
                        PriceNumberName + c + VoidID + c + VoidEmployeeName + c + TimeVoided + c + HoldTime + "'," + OrderTypeAmount + cc + _
                        DiscountAmount + ",'" + DiscountInternalID + "')"

                sql = Replace(sql, "$", "")
                sqlCollection.Add(sql)
            Next

        Next

        doSQLCollection(sqlCollection)

        MsgBox("Done with CheckRecords")
    End Sub

End Module

This post has been edited by KennethGarza: 28 July 2010 - 09:58 AM

[KennethGarza]

Ok everyone, Thanks but I was able to figure it out. To help people in the future I have posted my final code below. The previous version sledgehammered its way through the xml file finding about a million exception errors and thus slowing it down greatly.

Instead I took the opposite approach, designed the database to be dynamic and to have the program parse through the xml file naturally and then update the database as needed, creating new tables and new columns in tables when needed. Also uses a recursive method to parse the xml data. Something very new for me but it seems to be working great.

Here it is, I have many functions for sql that are in subroutines not in this code. Should be easy to figure out what they are supposed to do.

Thanks

Kenneth

Imports System.IO
Imports System.Xml

Module mod_focusxml

    Public c = "','"
    Public cc = ","
    Public qc = "',"
    Public cq = ",'"

    Public Tables As New Hashtable

    Public sqlCol As New Collection
    Public StoreID As String = ""
    Public BusinessDate As String = ""

    Public Sub GetDataBaseStructure()

        Tables.Clear()

        Dim sql = "Show tables"
        Dim tc As Collection = getSQL(sql)

        For a = 1 To tc.Count
            Dim s = Split(tc(a).ToString, ",")

            Tables.Add(s(0).ToString, New Hashtable)
        Next

        Dim tbl As New DictionaryEntry

        For Each tbl In Tables
            sql = "show columns from " + tbl.Key
            tc.Clear()
            tc = getSQL(sql)

            For a = 1 To tc.Count
                Dim ss = Split(tc(a).ToString, ",")
                Tables(tbl.Key).add(ss(0).ToString, "")
            Next
        Next
    End Sub


    Public Sub CheckColumn(ByVal TableName As String, ByVal Column As String, ByVal Value As String)

        Dim ColumnType As String = ""
        Dim DefaultValue As String = ""

        If Not Tables(TableName).containskey(Column) Then
            If Not IsNumeric(Value) Then
                ColumnType = "VARCHAR(45)"
                DefaultValue = "' '"
            Else
                ColumnType = "DOUBLE"
                DefaultValue = "0.00"
            End If

            Dim sql = "alter table " + TableName + " add column " + Column + " " + ColumnType + " not null default " + DefaultValue
            doSQL(sql)

            GetDataBaseStructure()
        End If
    End Sub

    Public Sub CheckTable(ByVal TableName As String)
        If Not Tables.ContainsKey(TableName) Then
            Dim sql = "create table `" + TableName + "` (" + _
                "`StoreID` VARCHAR(45) NOT NULL DEFAULT ' ', " + _
                "`BusinessDate` VARCHAR(45) NOT NULL DEFAULT ' ') " + _
                "ENGINE = InnoDB"

            doSQL(sql)

            GetDataBaseStructure()

        End If

    End Sub

    Public Function ReturnSQLfromXML(ByVal XMLdata As XmlElement, ByVal tblName As String, ByVal Storeid As String, ByVal BusinessDate As String) As String

        Dim Columns As String = ""
        Dim Values As String = ""

        Dim v As New Hashtable
        v.Add("StoreId", Storeid)
        v.Add("BusinessDate", BusinessDate)

        CheckTable(tblName)

        doSQL("Delete from " + tblName + " where BusinessDate='" + BusinessDate + "' and StoreID='" + Storeid + "'")

        For a = 0 To XMLdata.ChildNodes.Count - 1

            Dim col As String = "f" + XMLdata.ChildNodes(a).Name.ToString
            Dim val As String = Replace(XMLdata.ChildNodes(a).InnerText, "$", "")

            Dim aa As Integer = 0
            Dim col1 As String = col

            Do Until Not v.ContainsKey(col1)
                aa += 1
                col1 = col + aa.ToString
            Loop

            v.Add(col1, val)

            CheckColumn(tblName, col1, val)
        Next

        Dim sss As New DictionaryEntry
        Dim cnt = v.Count
        Dim aaa As Integer = 0

        For Each sss In v
            aaa += 1
            Columns += sss.Key
            Values += "'" + sss.Value + "'"

            If aaa <> cnt Then
                Columns += ","
                Values += ","
            End If
        Next

        Dim sql = "insert into " + tblName + " (" + Columns + ") values (" + Values + ")"

        Return sql

    End Function

    Public Function GetColumns(ByVal TableName As String) As Hashtable
        Dim ht As New Hashtable

        Dim tc As Collection = getSQL("show columns from " + TableName)
        For a = 1 To tc.Count
            Dim s = Split(tc(a).ToString, ",")
            ht.Add(s(0).ToString, "")
        Next

        Return ht
    End Function

    Public Sub ParseXML(ByVal Doc As XmlElement)
        Debug.Print(Doc.Name.ToString)
        If Doc.Name.ToLower = "summary" Then
            sqlCol.Add(ReturnSQLfromXML(Doc, "Summary", StoreID, BusinessDate))
        Else
            For Each n1 In Doc

                Dim XMLData As XmlElement = n1
                Dim TableName As String = XMLData.Name.ToString

                Dim xmlNodeType As XmlNodeType = xmlNodeType.Text

                Try
                    xmlNodeType = XMLData.FirstChild.FirstChild.NodeType
                Catch ex As Exception

                End Try

                If xmlNodeType = xmlNodeType.Element Then
                    ParseXML(XMLData)
                Else
                    sqlCol.Add(ReturnSQLfromXML(XMLData, TableName, StoreID, BusinessDate))
                End If
            Next
        End If

    End Sub

    Public Sub GetXML(Optional ByVal fn As String = "")
        Dim xmlDoc As New XmlDocument
        Dim xmlFile As String = ""

        If fn = "" Then
            Dim ofd As New OpenFileDialog
            ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
            ofd.ShowDialog()
            xmlFile = ofd.FileName
        Else
            xmlFile = fn
        End If

        If xmlFile = "" Then Exit Sub
        If Not fe(xmlFile) Then Exit Sub

        Dim s = Split(RemovePath(xmlFile), "-")
        StoreID = s(1).ToString

        'yyyy/MM/dd format'
        BusinessDate = s(0)(4).ToString + s(0)(5).ToString + s(0)(6).ToString + s(0)(7).ToString + "/" + _
            s(0)(0).ToString + s(0)(1).ToString + "/" + _
            s(0)(2).ToString + s(0)(3).ToString

        Dim XMLstring = readfromfilestr(xmlFile)
        XMLstring = Replace(XMLstring, "ÿ", "")

        xmlDoc.LoadXml(XMLstring)

        For Each n1 In xmlDoc.DocumentElement
            Dim XMLData = n1
            ParseXML(XMLData)
        Next

        doSQLCollection(sqlCol)

      
    End Sub
End Module