-
Jan 17th, 2018, 03:24 PM
#1
Thread Starter
Addicted Member
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
-
Jan 17th, 2018, 04:07 PM
#2
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
-
Jan 18th, 2018, 07:32 AM
#3
Thread Starter
Addicted Member
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)
-
Jan 18th, 2018, 08:01 AM
#4
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
-
Jan 18th, 2018, 08:22 AM
#5
Thread Starter
Addicted Member
Re: A little help importing from XML.
I posted the error but I don't see it
-
Jan 18th, 2018, 08:22 AM
#6
Thread Starter
Addicted Member
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.
-
Jan 18th, 2018, 08:39 AM
#7
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
-
Jan 18th, 2018, 09:02 AM
#8
Thread Starter
Addicted Member
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
-
Jan 18th, 2018, 09:45 AM
#9
Thread Starter
Addicted Member
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.
-
Jan 18th, 2018, 10:38 AM
#10
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|