1 Attachment(s)
Load xml into datagridview by column name and type
I have below xml. But when I try to load into datagridview. My code doesn't work well, can anyone help?
HTML Code:
<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..
Code:
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...
Attachment 91287
Re: Load xml into datagridview by column name and type
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
Re: Load xml into datagridview by column name and type
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
Code:
<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
Code:
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
