Results 1 to 5 of 5

Thread: [RESOLVED] Import large XML file into datatable: OutOfMemoryException was thrown

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2010
    Posts
    164

    Resolved [RESOLVED] Import large XML file into datatable: OutOfMemoryException was thrown

    Hi all,
    I have a large xml file (about 2Gb) and I'm trying to import it into dataset or better in SQL Server.
    I read that to solve issue of memory, it's better to use XmlReader...
    So I started with following code:
    Code:
     Dim settings As XmlReaderSettings = New XmlReaderSettings
            settings.DtdProcessing = DtdProcessing.Parse
            settings.ValidationType = ValidationType.Schema
    
            Dim reader As XmlReader = XmlReader.Create(TextBox1.Text, settings)
    
            While reader.Read
                If reader.NodeType = XmlNodeType.Element Then
                    Dim aa As XmlDocument = New XmlDocument
                    aa.LoadXml(reader.ReadOuterXml)
    
                End If
    
            End While
    but after aa.loadXml command I obtained "out of memory" message.
    How can I solve it?
    What is correct procedure to manage this file?

    I'm groping in the dark
    Thank in advanced
    gio

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Import large XML file into datatable: OutOfMemoryException was thrown

    try google search with key words = how to insert xml data into table in sql server
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Import large XML file into datatable: OutOfMemoryException was thrown

    Hmmm, I would say something is wrong if your program is running out of memory, if it's okay for it to run in 64-bit mode you could try setting the target CPU to 64-bit. This allows your program to use far more than 4gb of memory.
    Last edited by Peter Swinkels; Aug 17th, 2020 at 07:25 AM. Reason: fixed a typo

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Import large XML file into datatable: OutOfMemoryException was thrown

    Quote Originally Posted by giodepa View Post
    Hi all,
    I have a large xml file (about 2Gb) and I'm trying to import it into dataset or better in SQL Server.
    I read that to solve issue of memory, it's better to use XmlReader...
    So I started with following code:
    Code:
     Dim settings As XmlReaderSettings = New XmlReaderSettings
            settings.DtdProcessing = DtdProcessing.Parse
            settings.ValidationType = ValidationType.Schema
    
            Dim reader As XmlReader = XmlReader.Create(TextBox1.Text, settings)
    
            While reader.Read
                If reader.NodeType = XmlNodeType.Element Then
                    Dim aa As XmlDocument = New XmlDocument
                    aa.LoadXml(reader.ReadOuterXml)
    
                End If
    
            End While
    but after aa.loadXml command I obtained "out of memory" message.
    How can I solve it?
    What is correct procedure to manage this file?

    I'm groping in the dark
    Thank in advanced
    gio
    That's not how you use the XML Reader... what you did was open the XML Reader, looked at the first node, checked to see if it was an element node (it is) and then proceeded to laod the whole $^@ thing into an XMLDocument... what the heck? No... that's not now that was supposed to work.

    What you do is check the node's name... is it the node you're looking for? No? then keep .Read(ing) until you do find the node you are looking for... then read the .Text of the node. or the .Attributes (which ever is appropriate) that you need... and then keep .Read(ing) until you are done, or you run out of nodes...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2010
    Posts
    164

    Re: Import large XML file into datatable: OutOfMemoryException was thrown

    Hi all,
    thank for your reply.
    @tg
    you are right
    AS I didn't know the name element whose I needed, first I used command:
    Code:
    While reader.Read
    If reader.NodeType = XmlNodeType.Element
    dim elem as XElement
    elem=Xelement.ReadFrom(reader)
    ...
    End if
    End While
    When I found it finally I used following code:
    Code:
    If reader.NodeType = XmlNodeType.Element AndAlso (reader.Name = "managedObject") Then
    ...
    End if
    So I solved my issue

    gio

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width