VB.Net Loading Simple xml file to DataTable

Question

I am having trouble getting vb.net to load an xml file into a datatable for use in a datagridview and other text elements. I have tried most of the solutions I could find over the last 2 days of searching but nothing seems to work. From what I can tell this should be just a simple DataSource.ReadXML command but when I do this and then set the DataGridVew1 datasource nothing shows up. I have tried multiple combos of reading to the DataTable and DataSource but cant get either to work.

I'm not sure what the correct approach is here. Should I be using 'ReadXML' into the DataSet or DataTable? If I read into the DataSet how does the data get into the correct table? Right now I am only working with 1 table but once I get this figured out I will need to read data from multiple different xml files and put them into different DataTables.

Here is a sample of the xml file I am using. I shortened it since there are approx 100 records in the document. This xml file was generated in excel to save time from typing everything out:

<?xml version="1.0" encoding="utf-8" ?>
<BlankData  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <record>
        <SizeNum>#1 - 45</SizeNum>
        <Height>8</Height>
        <Top>3</Top>
        <Bottom>7.75</Bottom>
        <Weight>0</Weight>
        <TrimmedWeight>0</TrimmedWeight>
        <Wrap>false</Wrap>
    </record>
    <record>
        <SizeNum>#1 - 90</SizeNum>
        <Height>8</Height>
        <Top>1.75</Top>
        <Bottom>11.25</Bottom>
        <Weight>0</Weight>
        <TrimmedWeight>0</TrimmedWeight>
        <Wrap>false</Wrap>
    </record>
    <record>
        <SizeNum>#10 - 45 </SizeNum>
        <Height>20</Height>
        <Top>4.875</Top>
        <Bottom>16.75</Bottom>
        <Weight>0</Weight>
        <TrimmedWeight>0</TrimmedWeight>
        <Wrap>false</Wrap>
    </record>
    <record>
        <SizeNum>#10 - 90 </SizeNum>
        <Height>14</Height>
        <Top>1.125</Top>
        <Bottom>19.5</Bottom>
        <Weight>0</Weight>
        <TrimmedWeight>0</TrimmedWeight>
        <Wrap>false</Wrap>
    </record>
    <record>
        <SizeNum>#11 - 45 </SizeNum>
        <Height>23</Height>
        <Top>6</Top>
        <Bottom>21.375</Bottom>
        <Weight>0</Weight>
        <TrimmedWeight>0</TrimmedWeight>
        <Wrap>false</Wrap>
    </record>
</BlankData>

I added this xml file to the project by adding a new xml file item and naming it BlankData. There is also a DataSource called PressData with a Table called BlankData and it has 7 columns with names that match the above xml sample. Not sure if its correct to have the ml file and datatable share the same name, before this iteration I had a differnt name for the xml file and tablename but I couldnt get it to work so I decided to try giving them the same name

I have tired to load the xml to the datasource with the below code

PressData.ReadXml(My.Application.Info.DirectoryPath & "\BlankData.xml")
DataGridView1.DataSource = PressData

which results in a blank datagridview box

and tired loading the xml to the datatable inside the datasource with this code

PressData.BlankData.ReadXml(My.Application.Info.DirectoryPath & "\BlankData.xml")
DataGridView1.DataSource = PressData.BlankData

which results in the datagridview showing the 7 columns but no row data.

I have tried a few other minor changes which I cant get to work. Does anyone have any suggestions on what to try? As I said earlier this seems like it should be simple but nothing I do seems to make a difference in the results, it is awlays one of the two cases above.

Is it possible I have a problem with the xml file?

Thanks for the help!

Daryan

Answer 1

To load xml data, you should use a dataset, and not a datatable.

When the xml is read, then the dataset (which is a collection of tables) will load the data into the first table in the dataset.

So, I just pasted your data into a test txt file.

Hence this code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim sFile As String =
        "c:\test\test2\data.txt"

    Dim dt As New DataSet
    dt.ReadXml(sFile)

    DataGridView1.DataSource = dt.Tables(0)


End Sub

And the result is this:

So, using a dataset works just fine. A dataset in place of a datatable is required, since xml imports can result in multiple tables.

Given your data only results in one datatable, then using the first datatable in the dataset array, and assigning that to say the example datagridview works fine as above shows.