Results 1 to 10 of 10

Thread: A little help importing from XML.

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Apr 2016
    Posts
    155

    A little help importing from XML.

    I am trying to import an xml file but am having problems.

    Code is
    Dim Needlist As New DataTable
    Call oXml.GetXMLData(SUPPLIER_NEED_LIST, Needlist, False, "")

    Sub GetXMLData(ByRef xmlfilename As String, ByRef dt As DataTable, ByRef haveschema As Boolean, ByRef xsdfilename As String)

    If haveschema = True Then
    dt.ReadXmlSchema(xsdfilename)
    dt.ReadXml(xmlfilename)
    Else
    Dim ds As New DataSet("XmlData")

    ds.Tables.Add(dt)

    If File.Exists(xmlfilename) Then
    Dim XMLReader As StringReader = New System.IO.StringReader(xmlfilename)
    dt.ReadXml(XMLReader)
    End If
    End If

    End Sub

    Excerpt of data is

    <?xml version="1.0"?>
    <suppliers>
    <supplier id="000002">
    <fvendno>000002</fvendno>
    </supplier>
    <supplier id="000005">
    <fvendno>000005</fvendno>
    </supplier>
    <supplier id="000009">
    <fvendno>000009</fvendno>
    </supplier>
    <supplier id="000017">
    <fvendno>000017</fvendno>
    </supplier>
    </suppliers>

    Any help would be great. Thanks

  2. #2
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: A little help importing from XML.

    Based on the limited information about the ultimate goal I put this together. Hope it helps.

    Code:
    Public Class Form1
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim theXML As XElement
    
            'to load from a file
            '  theXML = XElement.Load("path to file here")
    
            'for testing
            theXML = <suppliers>
                         <supplier id="000002i">
                             <fvendno>000002</fvendno>
                         </supplier>
                         <supplier id="000005i">
                             <fvendno>000005</fvendno>
                         </supplier>
                         <supplier id="000009i">
                             <fvendno>000009</fvendno>
                         </supplier>
                         <supplier id="000017i">
                             <fvendno>000017</fvendno>
                         </supplier>
                     </suppliers>
    
            Dim foo As New Suppliers(theXML)
            DataGridView1.DataSource = foo.ToDatatable
    
        End Sub
    
        Public Class Suppliers
            Public SuppliersList As New List(Of Supplier)
    
            Public Sub New(FileXE As XElement)
                For Each el As XElement In FileXE.Elements
                    Me.SuppliersList.Add(New Supplier(el))
                Next
            End Sub
    
            Public Function ToDatatable() As DataTable
                Dim rv As New DataTable
    
                'add columns
                rv.Columns.Add("SupID", GetType(String))
                rv.Columns.Add("FVendNo", GetType(String))
    
                For Each itm As Supplier In SuppliersList
                    Dim rw As DataRow = rv.NewRow
                    rw("SupID") = itm.SupplierID
                    rw("FVendNo") = itm.FVendor
                    rv.Rows.Add(rw)
                Next
                Return rv
            End Function
        End Class
    
        Public Class Supplier
    
            Private _theXML As XElement
            Public Sub New(el As XElement)
                Me._theXML = el 'this is byref
            End Sub
    
            Public Property SupplierID() As String
                Get
                    Return Me._theXML.@id
                End Get
                Set(ByVal value As String)
                    Me._theXML.@id = value
                End Set
            End Property
    
            Public Property FVendor() As String
                Get
                    Return Me._theXML.<fvendno>.Value
                End Get
                Set(ByVal value As String)
                    Me._theXML.<fvendno>.Value = value
                End Set
            End Property
    
    
        End Class
    End Class
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Apr 2016
    Posts
    155

    Re: A little help importing from XML.

    The data is in an XML file and I want to import it into a datatable. I am using imports System.xml and System.xml.serialization in my dll sub GetXMLData. I error out when it gets to dt.Read(XMLReader)

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

    Re: A little help importing from XML.

    I error out when it gets to dt.Read(XMLReader)
    OOOOH! I LOVE this game! It's the game where we try to read your mind even though we're not mind readers! Sweet! OK. My first guess as to the problem: The door fell off its hinges! No? OK... Guess number two: The wheels fell off you car! Still no? Hmmm.... OK... Third and final guess: Your fingers exploded! Yes! That must be it. Because that's the only explanation for you not being able to type any details about the error or the problem.

    Yes, I'm mocking you. No one goes into the dotctor's office and simply states "it hurts" and expects him to guess what the problem is. No. People usually give details. Thinks like "I was skateboarding." And "I was running up the half-pipe when I fell." and "And busted my arm." ... See? See how the little details go a long way into solving the problem? I mean you'd be plenty pissed if you told the doctor that "It hurts" and he starts examining your stomach, wouldn't you? But that's because you didn't tell him that it's your arm that hurts. Now you've gone and wasted everyone's time.

    -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
    Apr 2016
    Posts
    155

    Re: A little help importing from XML.

    I posted the error but I don't see it

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Apr 2016
    Posts
    155

    Re: A little help importing from XML.

    An unhandled exception of type 'System.Xml.XmlException' occurred in System.Xml.dll

    Additional information: Data at the root level is invalid. Line 1, position 1.

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: A little help importing from XML.

    That's better... the format of the XML as you have it isn't compatible with loading directly into a datatable... did you try dbassnett's solution? It gets around that by loading the XML into a class, then adding that to the datatable and returning it. All you need to do is load the XML file into an element object, pass it to the supplier class and then call the todatatable method and it will return a populated datatable.

    -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??? *

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Apr 2016
    Posts
    155

    Re: A little help importing from XML.

    I am trying to build a dll to call when ever I want to load an xml file so I only pass the datatable to the sub with the location of the xmlfile. Therefor the xml file could have many different "fields" of data. The above looks to be specific to this one xml file. I currently now have the code running without error but nothing is imported into the datatable or dataset. the XMLReader length is 25358 so I know it is getting the data but the data is not going from the XMLReader to the dataset.

    Dim ds As New DataSet("XmlData")

    ds.Tables.Add(dt)

    If File.Exists(xmlfilename) Then
    Dim XMLReader As Stream = New System.IO.FileStream(xmlfilename, FileMode.Open, FileAccess.Read)
    dt.BeginLoadData()
    ds.ReadXml(XMLReader, XmlReadMode.Auto)
    dt.EndLoadData()
    End If

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Apr 2016
    Posts
    155

    Re: A little help importing from XML.

    OK, I have used the code above and it does get the data in to the datatable. I will have to adapt it to be able to use any xml file.

    Any help with that would be appreciated. Thanks all.

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Apr 2016
    Posts
    155

    Re: A little help importing from XML.

    OK, I got it. Here is the code. Thanks to everyone for helping!!!

    Sub GetXMLData(ByRef xmlfilename As String, ByRef dt As DataTable)

    Dim theXML As XElement
    theXML = XElement.Load(xmlfilename)

    Dim foo As New XMLs(theXML)
    foo.ToDatatable(dt)

    End Sub

    End Class
    Public Class XMLs
    Public XMLList As New List(Of Xml)

    Public Sub New(FileXE As XElement)
    For Each el As XElement In FileXE.Elements
    Me.XMLList.Add(New XML(el))
    Next
    End Sub

    Public Function ToDatatable(rv As datatable) As DataTable

    'Get the columns
    Dim columnnames(rv.Columns.Count - 1) As String
    Dim colname As String
    For i = 0 To rv.Columns.Count - 1
    colname = rv.Columns.Item(i).Caption
    columnnames(i) = colname
    Next

    For Each itm As XML In XMLList
    Dim rw As DataRow = rv.NewRow
    'Do for each field
    For i = 0 To rv.Columns.Count - 1
    'rw("SupID") = itm.SupplierID
    'rw("FVendNo") = itm.FVendor
    If i = 0 Then
    rw(columnnames(i)) = itm.fieldID
    i = i + 1
    End If
    rw(columnnames(i)) = itm.NextFieldID(columnnames(i))
    rv.Rows.Add(rw)
    Next
    Next

    Return rv

    End Function
    End Class

    Public Class XML

    Private _theXML As XElement
    Public Sub New(el As XElement)
    Me._theXML = el 'this is byref
    End Sub

    Public Property fieldID() As String
    Get
    Return Me._theXML.@id
    End Get
    Set(ByVal value As String)
    Me._theXML.@id = value
    End Set
    End Property

    Public Property NextFieldID(fieldname As String) As String
    Get
    'Return Me._theXML.<fieldname>.Value
    Return Me._theXML.Elements(fieldname).Value
    End Get
    Set(ByVal value As String)
    'Me._theXML.<fieldname>.Value = value
    Me._theXML.Elements(fieldname).Value = value
    End Set
    End Property

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