Large XML files in dataset (out of memory) RRS feed

  • Question

  • User490317677 posted

    I'm trying to read a 1GB XML file with almost 1 million records through a URL and store <file> in a dataset and save them to a database, but after 10 minutes, the application will crash and throw an Out of Memory Exception.

    The XML looks like this:

    <ICECAT-interface xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://data.icecat.biz/xsd/files.index.xsd">
      <files.index Generated="20200220011050">
        <file path="export/freexml.int/INT/1399.xml" Product_ID="1399" Updated="20200122151223" Quality="ICECAT" Supplier_id="1" Prod_ID="C4872A" Catid="377" On_Market="1" Model_Name="80" Product_View="333140" HighPic="http://images.icecat.biz/img/gallery/1399_4084669833.jpg" HighPicSize="2734733" HighPicWidth="4200" HighPicHeight="2772" Date_Added="20051027000000" Limited="No"></file>
        <file path="export/freexml.int/INT/1400.xml" Product_ID="1400" Updated="20200122151313" Quality="ICECAT" Supplier_id="1" Prod_ID="C4874A" Catid="377" On_Market="1" Model_Name="80" Product_View="283127" HighPic="http://images.icecat.biz/img/gallery/1400_6603855084.jpg" HighPicSize="2734913" HighPicWidth="4200" HighPicHeight="2952" Date_Added="20051027000000" Limited="No"></file>

    Sub Main():

    Class Module1
        Public Shared Sub Main()
            Dim url As String = "http://data.Icecat.biz/export/freexml/EN/daily.index.xml"
        End Sub
    End Class


    Public Shared Function ProcessXMLFeedURL(MyURL As String) As Boolean
        Dim OK As Boolean = False
            Dim rssReq As WebRequest = WebRequest.Create(MyURL)
            Dim username As String = ""
            Dim password As String = ""
            Dim encoded As String = System.Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(username + ":" + password))
            rssReq.Headers.Add("Authorization", "Basic " + encoded)
            '//Get the WebResponse
            Dim rep As WebResponse = rssReq.GetResponse()
            '//Read the Response in a XMLTextReader
            Dim xtr As XmlTextReader = New XmlTextReader(rep.GetResponseStream())
            '// Set up the connection to the SQL server
            Dim MyConnectionString As String = "Data Source=...."
            Dim Connection As SqlConnection = New SqlConnection(MyConnectionString)
            Dim MyServer As Server = New Server(New ServerConnection(Connection))
            Dim db As Database = New Database(MyServer, "xxxxxx")
            '//Create a new DataSet
            Dim ds As DataSet = New DataSet()
            '// Parse tables
            Dim Mytable As Table
            Dim MyTableName As String = ds.Tables(1).TableName
            If Not HaveTable(MyConnectionString, MyTableName) Then
                    '// Create the table
                        Mytable = New Table(db, MyTableName)
                    Catch ex As Exception
                        Dim ii As Integer = 0
                    End Try
                    '// create the columns
                    Dim Mycolumn As Column = New Column()
                For Each dc As DataColumn In ds.Tables(1).Columns
                    Mycolumn = New Column(Mytable, dc.ColumnName)
                    Mycolumn.DataType = getdatatype(dc.DataType.ToString)
                Dim PrimaryKeys() As DataColumn = ds.Tables(1).PrimaryKey
                Dim PrimaryKey As DataColumn
                    For Each PrimaryKey In PrimaryKeys
                        Dim Myindex As Index = New Index(Mytable, PrimaryKey.ColumnName)
                        Myindex.IndexKeyType = IndexKeyType.DriPrimaryKey
                        Myindex.IndexedColumns.Add(New IndexedColumn(Myindex, PrimaryKey.ColumnName))
                End If
                Using MyConnection As SqlConnection = New SqlConnection(MyConnectionString)
                    Using bulkcopy As SqlBulkCopy = New SqlBulkCopy(MyConnection)
                    bulkcopy.DestinationTableName = "[databasename].[dbo].[" + MyTableName + "]"
                    Catch ex As Exception
                            Dim iw As Integer = 0
                        End Try
                    End Using
                End Using
        Catch ex As Exception
            Throw ex '// Do errorhanddling here
        End Try
        Return OK
    End Function
    Thursday, February 20, 2020 8:01 PM

All replies