Load xml into datagridview by column name and type

[Jameslooi]

I have below xml. But when I try to load into datagridview. My code doesn't work well, can anyone help?

<Records>
 <Record>
 <Product>
  <Column name="Serial">N110879987</Column>
  <Column name="Product">QWX4T6YU</Column>
  <Column name="Date">2012-08-10</Column>
 </Product>
 <Operations>
 <Operation type="SETUP">
  <Column name="TimeStamp">2012-08-10</Column>
  <Column name="Status">Success</Column>
 </Operation>
 <Operation type="RUN">
  <Column name="TimeStamp">2012-08-11</Column>
  <Column name="Status">Done</Column>
 </Operation>
 </Operations>
</Record>
 <Record>
 <Product>
  <Column name="Serial">N110879988</Column>
  <Column name="Product">QWX4T6YU</Column>
  <Column name="Date">2012-08-12</Column>
 </Product>
 <Operations>
 <Operation type="SETUP">
  <Column name="TimeStamp">2012-08-12</Column>
  <Column name="Status">Success</Column>
 </Operation>
 <Operation type="RUN">
  <Column name="TimeStamp">2012-08-12</Column>
  <Column name="Status">Done</Column>
 </Operation>
 </Operations>
</Record>
</Records>

Below is my code, it doesn't work..

Dim dt As New DataTable
Dim newRow As DataRow
Dim ds As New DataSet()
Dim doc As New Xml.XmlDocument
doc.Load("C:\Temp\N11.xml")
 
        Dim dt As New DataTable
        Dim newRow As DataRow
        dt.Columns.Add("Serial")
        dt.Columns.Add("Product")
        dt.Columns.Add("Date")
 
        dt.Columns.Add("Operation")
        dt.Columns.Add("TimeStamp")
        dt.Columns.Add("Status")
 


For Each n As XmlNode In doc.SelectNodes("Records/Record/Product/Column")
   newRow = dt.NewRow
           
   newRow(n.Attributes("name").Value) = n.InnerText
        
  For Each n1 As XmlNode In doc.SelectNodes("Records/Record/Operations/Operation")
    newRow = dt.NewRow
    newRow("Operation") = n1.Attributes("type").Value

     For Each n2 As XmlNode In doc.SelectNodes("Records/Record/Operations/Operation[@type='" & n1.Attributes("type").Value & "']/Column")
                   
         newRow(n2.Attributes("name").Value) = n2.InnerText

      Next
      dt.Rows.Add(newRow)
   Next
Next
 
  DataGridView1.DataSource = dt

Below is my idle output...

[kareninstructor]

I would suggest using a DataSet to read the xml file. Please note that a Data Visualizer will assist, see link below, go to bottom of the page.
http://odetocode.com/Articles/425.aspx

[kareninstructor]

I had a few minutes to spare. If the xml source is created by you then consider the following which alters the xml structure

XML file XMLFile1.xml

<Records>
  <Record>
    <Product>
      <Serial>N110879987</Serial>
      <ProductName>QWX4T6YU</ProductName>
      <SomeDate>2012-08-10</SomeDate>
    </Product>
    <Operations>
      <Operation type="SETUP">
        <TimeStamp>2012-08-10</TimeStamp>
        <Status>Success</Status>
      </Operation>
      <Operation type="RUN">
        <TimeStamp>2012-08-11</TimeStamp>
        <Status>Done</Status>
      </Operation>
    </Operations>
  </Record>
  <Record>
    <Product>
      <Serial>N110879988</Serial>
      <ProductName>QWX4T6YU</ProductName>
      <SomeDate>2012-08-12</SomeDate>
    </Product>
    <Operations>
      <Operation type="SETUP">
        <TimeStamp>2012-08-12</TimeStamp>
        <Status>Success</Status>
      </Operation>
      <Operation type="RUN">
        <TimeStamp>2012-08-12</TimeStamp>
        <Status>Done</Status>
      </Operation>
    </Operations>
  </Record>
</Records>

The code was done in VS2010 but hopefully will work in VS2005

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ds As New DataSet
        ds.ReadXml("XMLFile1.xml")
        '
        ' The following for/next display hidden columns to the IDE output window
        '
        Console.WriteLine("Product columns hidden")
        For Each col As DataColumn In ds.Tables("Product").Columns
            If col.ColumnMapping = MappingType.Hidden Then
                Console.WriteLine("    [{0}]", col.ColumnName)
            End If
        Next

        Console.WriteLine("Operation columns hidden")
        For Each col As DataColumn In ds.Tables("Operation").Columns
            If col.ColumnMapping = MappingType.Hidden Then
                Console.WriteLine("    [{0}]", col.ColumnName)
            End If
        Next

        Dim ProductIdentifiers As DataView = _
            New DataView(ds.Tables("Product") _
                         .DefaultView _
                         .ToTable("Product", True, "Record_id"))

        For Each item As DataRowView In ProductIdentifiers
            Dim FirstRow As Boolean = False
            ds.Tables("Product").DefaultView.RowFilter = "Record_id=" & item.Item("Record_id").ToString
            ds.Tables("Operation").DefaultView.RowFilter = "Operations_id=" & item.Item("Record_id").ToString
            For Each row As DataRow In ds.Tables("Product").Rows
                If Not FirstRow Then
                    Dim CurrRow As DataRowView = ds.Tables("Operation").DefaultView.Item(0)
                    DataGridView1.Rows.Add(New Object() {row(0), row(1), row(2), CurrRow(0), CurrRow(1), CurrRow(2)})
                    FirstRow = True
                Else
                    For x As Integer = 1 To ds.Tables("Operation").DefaultView.Count - 1
                        Dim SubRow As DataRowView = ds.Tables("Operation").DefaultView.Item(x)
                        DataGridView1.Rows.Add(New Object() _
                        {Nothing, Nothing, Nothing, SubRow.Item(0), SubRow.Item(1), SubRow.Item(2)})
                    Next
                End If
            Next
        Next
    End Sub
End Class

Screenshot where I added six DataGridViewColumn to a DataGridView