Complex XML file to Data Set

Question

User1717218719 posted

Hi All,

I am trying to display my XML file in a DataSet. I have a basic XML file working but I can't seem to read a more complex XML into DataSet.

I have lines in the xml that look like this:

 <Item1>
        <Number>1</Number>
        <Code>123</Code>
        <Description>AB</Description>
        <Amount Name="Invoice">50.00</Amount>
        <Amount Name="Paid">10.00</Amount>
        <Product>
            <ProductName>Product57</ProductName>
                <ProductAmount>40</ProductAmount>
        </Product>
        <TotalAmount>763.860</TotalAmount>
        <ID>2</ID>
</Item1>

When I run my code it skips the lines:

Inside the <prouct> and the ones that are like: <Amount Name="Invoice">

Is there a way of editing my code to read in nodes of this style ?

My code:

Private Sub btnSelect_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
    Dim openFileDialog As OpenFileDialog = New OpenFileDialog()
    If openFileDialog.ShowDialog() = DialogResult.OK Then
        Dim xmlFilePath As String = openFileDialog.FileName
        Using ds As DataSet = New DataSet()
            ds.ReadXml(xmlFilePath)
            DataGridView1.DataSource = ds.Tables(0)

            'For Each table As DataTable In ds.Tables
            '    For Each row As DataRow In table.Rows
            '        For Each column As DataColumn In table.Columns
            '            Label1.Text += vbCr & row(column) ' "</br>" & row(column)
            '        Next
            '    Next
            'Next

        End Using
    End If
End Sub

Thanks

Erica

Answer 1

User475983607 posted

First convert the XML to a class using Visual Studio.  

https://improveandrepeat.com/2017/08/paste-xml-as-class-in-visual-studio-2017/

Next, read the openly published XML serialization docs to deserialize the XML.

https://docs.microsoft.com/en-us/dotnet/api/system.xml.serialization.xmlserializer.deserialize?view=netframework-4.8

Answer 2

User1535942433 posted

Hi E.RU,

According to your description,as far as I think,you could use XML Linq or ReadXml.

Just like this:

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Xml
Imports System.Xml.Linq

Namespace ConsoleApplication1
    Class Program
        Const FILENAME As String = "c:\temp\test.xml"

        Private Shared Sub Main(ByVal args As String())
            Dim doc As XDocument = XDocument.Load(FILENAME)
            Dim elements As List(Of Element) = doc.Descendants("Element").[Select](Function(x) New Element() With {
                .type = CStr(x.Attribute("Type")),
                .indice = CInt(x.Attribute("Indice")),
                .name = x.FirstNode.ToString(),
                .haut = CDec(x.Descendants("Haut").FirstOrDefault()),
                .bas = CDec(x.Descendants("Bas").FirstOrDefault()),
                .points = x.Elements("Point").[Select](Function(y) New Point() With {
                    .id = CStr(y.Attribute("id")),
                    .x = CDec(y.Attribute("X")),
                    .y = CDec(y.Attribute("Y")),
                    .value = CInt(y)
                }).ToList()
            }).ToList()
        End Sub
    End Class

    Public Class Element
        Public Property type As String
        Public Property indice As Integer
        Public Property name As String
        Public Property haut As Decimal
        Public Property bas As Decimal
        Public Property points As List(Of Point)
    End Class

    Public Class Point
        Public Property id As String
        Public Property x As Decimal
        Public Property y As Decimal
        Public Property value As Integer
    End Class
End Namespace

using Xml putting into a DataTable:

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Xml
Imports System.Xml.Linq
Imports System.Data

Namespace ConsoleApplication1
    Class Program
        Const FILENAME As String = "c:\temp\test.xml"

        Private Shared Sub Main(ByVal args As String())
            Dim dt As DataTable = New DataTable()
            dt.Columns.Add("Type", GetType(String))
            dt.Columns.Add("Indice", GetType(Integer))
            dt.Columns.Add("Name", GetType(String))
            dt.Columns.Add("Haut", GetType(Decimal))
            dt.Columns.Add("Bas", GetType(Decimal))
            dt.Columns.Add("Id", GetType(String))
            dt.Columns.Add("X", GetType(Decimal))
            dt.Columns.Add("Y", GetType(Decimal))
            dt.Columns.Add("Value", GetType(Integer))
            Dim doc As XDocument = XDocument.Load(FILENAME)

            For Each element As XElement In doc.Descendants("Element")
                Dim type As String = CStr(element.Attribute("Type"))
                Dim indice As Integer = CInt(element.Attribute("Indice"))
                Dim name As String = element.FirstNode.ToString()
                Dim haut As Decimal = CDec(element.Descendants("Haut").FirstOrDefault())
                Dim bas As Decimal = CDec(element.Descendants("Bas").FirstOrDefault())

                For Each point As XElement In element.Elements("Point")
                    Dim id As String = CStr(point.Attribute("id"))
                    Dim x As Decimal = CDec(point.Attribute("X"))
                    Dim y As Decimal = CDec(point.Attribute("Y"))
                    Dim value As Integer = CInt(point)
                    dt.Rows.Add(New Object() {type, indice, name, haut, bas, id, x, y, value})
                Next
            Next
        End Sub
    End Class
End Namespace

ReadXml:

https://docs.microsoft.com/en-us/dotnet/api/system.data.dataset.readxml?view=net-5.0

Best regards,

Yijing Sun

Answer 3

User1717218719 posted

Hi mgebhard,

Thank you for your reply.

I have looked into this and it seems like a great option. I have tried to find do this code in visual basics but unfortune my C# conversion skills are not great and I am unsure about the part of reading the openly published XML serialization docs to deserialize the XML

I have done the paste special (I am using same xml file CT007.xml as in example for testing purposes). It seems to output true false true etc to the datatable...Im assuming this is because of the deserialize process ? could you possible help with some sample code for the second step to output xml file data to dataset rather than the true false true true etc which is happening now.

when I wrote out the code to VB it looked like this:

    Private Sub btnSelect_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        Dim openFileDialog As OpenFileDialog = New OpenFileDialog()
        If openFileDialog.ShowDialog() = DialogResult.OK Then
            Dim xmlFilePath As String = openFileDialog.FileName
            Using ds As DataSet = New DataSet()
                ds.ReadXml(xmlFilePath)

                Call Main()

                DataGridView1.DataSource = ds.Tables(0)

            End Using


        End If
    End Sub

    Public Sub Main()
        Dim pathToXmlFile = "C:\CT007.xml"
        Dim result As CT007 = New CT007()
        Dim serializer As XmlSerializer = New XmlSerializer(GetType(CT007))

        Using fileStream As FileStream = New FileStream(pathToXmlFile, FileMode.Open)
            result = CType(serializer.Deserialize(fileStream), CT007)
        End Using

    End Sub

Answer 4

User1535942433 posted

Hi E.RU,

As far as I think,you could do like this:

Private Sub SurroundingSub()
    Dim xmlFile As String = "CT007.xml"
    Dim dataSet As DataSet = New DataSet()
    dataSet.ReadXml(xmlFile, XmlReadMode.InferSchema)

    For Each table As DataTable In dataSet.Tables
        Console.WriteLine(table)

        For i As Integer = 0 To table.Columns.Count - 1
            Console.Write(vbTab & table.Columns(i).ColumnName.Substring(0, Math.Min(6, table.Columns(i).ColumnName.Length)))
        Next

        Console.WriteLine()

        For Each row In table.AsEnumerable()

            For i As Integer = 0 To table.Columns.Count - 1
                Console.Write(vbTab & row(i))
            Next

            Console.WriteLine()
        Next
    Next
End Sub

Best regards,

Yijing Sun

Answer 5

User1717218719 posted

Hi Yijing Sun,

Thank you for your reply when I ran your code I got it to work and write out the correct output in the console however when I tried to put it in a display it in my gridview it appears empty ? I'm not sure why it is not working like that

Thanks

Erica

Answer 6

User1717218719 posted

When I try the following code it outputs what I want to the console perfectly but not to the datagridview. In the datagridview it only outputs the last row of the xml file. I am unsure as to why this is happening. my end goal is to display in the datagrid view then export it to an excel file. could anyone please help ?

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xmlFile As String = "CT007.xml"
        Dim dataSet As DataSet = New DataSet()
        dataSet.ReadXml(xmlFile, XmlReadMode.InferSchema)

        For Each table As DataTable In dataSet.Tables
            Console.WriteLine(table)

            '--GETS HEADER
            For i As Integer = 0 To table.Columns.Count - 1
                Console.Write(vbTab & table.Columns(i).ColumnName.Substring(0, Math.Min(20, table.Columns(i).ColumnName.Length)))
            Next
            Console.WriteLine() '--OUTPUTS HEADERS 

            For Each row In table.AsEnumerable()

                For i As Integer = 0 To table.Columns.Count - 1
                    Console.Write(vbTab & row(i)) '--CONTENTS UNDER HEADER
                Next

                Console.WriteLine() '-- OUTPUTS HEADER CONTENTS 
             Next

            DataGridView1.DataSource = Table

        Next
    End Sub

Thanks so much for all the help So far I am learning lots !!

Answer 7

User475983607 posted

Your code loops over tables in a dataset and sets the data source to the last table found.  Take a few moments to run your logic through the Visual Studio debugger to understand how your code actually works.

Answer 8

User1535942433 posted

Hi E.RU,

According to your description,you have two problems.One is binding the data in dataGrid. The second id export dataGrid to excel.

You could do like this:

Bind data:

Private Sub SurroundingSub()
    Dim dataSet As DataSet = New DataSet()
    DataGrid.DataSource = ds
    DataGrid.DataBind()
End Sub

Export:

Private Sub SurroundingSub()
    Dim resp As HttpResponse = System.Web.HttpContext.Current.Response
    resp.Clear()
    resp.AddHeader("Content-Disposition", "attachment;filename=" & filename)
    resp.ContentType = "text/csv"
    Dim csv As String = GetCsv(headers, data)
    Dim buffer As Byte() = resp.ContentEncoding.GetBytes(csv)
    resp.AddHeader("Content-Length", buffer.Length.ToString())
    resp.BinaryWrite(buffer)
    resp.[End]()
End Sub

Best regards,

Yijing Sun

Answer 9

User1717218719 posted

Hi Yij Sun,

Thank you for your reply.

where in my code should I add the bind as I am still struggling to get all the values and not have it loop and display the last line only.

Also for the line Dim csv As String = GetCsv(headers, data)... is headers and data a variable I have to declare ?

Thanks a million

Answer 10

User1717218719 posted

Hi Mgebhard,

thank you for your help, I have looked ad my code and ran some break points and tried to debug but with no success.

Could you please help with where I should bind the data as my code displays headers then contents and I am finding it a little confusing as I want to display everything in the gridview.

Thanks

Answer 11

User1535942433 posted

Hi E.RU,

If you need read and bind xml data to gridview,you could refer to below these codes:

<div>

    <h3>Load XML to a "DataSet" and Bind with the GridView.</h3>
    <br /> 
    <asp:GridView ID="GridView1" 
        CellPadding="3" CellSpacing="0" 
        runat="server">
        
        <HeaderStyle BackColor="#989898" ForeColor="white" />    
    </asp:GridView>
</div>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind_GridView_With_XML();
        }
    }

    private void Bind_GridView_With_XML()
    {
        DataSet ds = new DataSet();

        ds.ReadXml(MapPath("~/library.xml"));

        // BIND THE DataSet WITH GRIDVIEW.

        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}

And The export you could refer to below code:

Protected Sub btnExport_Click(sender As Object, e As EventArgs)
    If gridview.Rows.Count > 0 Then
        Try
            gridview.Columns(0).Visible = False
            Response.ClearContent()
            Response.Buffer = True
            Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "TestPage.xls"))
            Response.ContentEncoding = Encoding.UTF8
            Response.ContentType = "application/ms-excel"
            Dim sw As New StringWriter()
            Dim htw As New HtmlTextWriter(sw)
            gridview.RenderControl(htw)
            Response.Write(sw.ToString())
            Response.[End]()

        Catch ex As Exception
        Finally
            gridview.Columns(0).Visible = True
        End Try
    End If
End Sub

Best regards,

Yijing Sun

Answer 12

User1717218719 posted

Thanks Yijing Sun,

My code now looks like the below. I am able to export to excel thanks to your code.

The gridview is still only displaying the last line of the xml file. I have tried to debug it but no luck in catching the problem.

Mgebhard mentioned that the code loops over tables in a dataset ? I am not sure how to fix this if this is the cause ?

    Protected Sub Upload(sender As Object, e As EventArgs)
        Dim FileUpload1 As FileUpload = TryCast(FindControl("FileUpload1"), FileUpload)

        If FileUpload1.HasFile And FileUpload1.FileContent IsNot Nothing Then

            Dim xmlFile As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

            Dim dataSet As DataSet = New DataSet()
            dataSet.ReadXml(xmlFile, XmlReadMode.InferSchema)

            For Each table As DataTable In dataSet.Tables
                Console.WriteLine(table)

                '--GETS HEADER
                For i As Integer = 0 To table.Columns.Count - 1
                    Console.Write(vbTab & table.Columns(i).ColumnName.Substring(0, Math.Min(20, table.Columns(i).ColumnName.Length)))
                    Dim header As String = (vbTab & table.Columns(i).ColumnName.Substring(0, Math.Min(20, table.Columns(i).ColumnName.Length)))

                Next
                Console.WriteLine() '--OUTPUTD HEADERS 

                For Each row In table.AsEnumerable()

                    For i As Integer = 0 To table.Columns.Count - 1
                        Console.Write(vbTab & row(i)) '--CONTENTS UNDER HEADER
                        Dim Data As String = vbTab & row(i)

                    Next

                    Console.WriteLine() '-- OUTPUTS HEADER CONTENTS 

                Next

                GridView1.DataSource = dataSet
                GridView1.DataBind()

            Next

        End If

    End Sub
        Protected Sub btnExport_Click1(sender As Object, e As EventArgs) Handles btnExport.Click
        If GridView1.Rows.Count > 0 Then
            Try
            GridView1.Columns(0).Visible = False
            Response.ClearContent()
                Response.Buffer = True
                Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "xmlFile.xls"))
                Response.ContentEncoding = Encoding.UTF8
                Response.ContentType = "application/ms-excel"
                Dim sw As New StringWriter()
                Dim htw As New HtmlTextWriter(sw)
                GridView1.RenderControl(htw)
                Response.Write(sw.ToString())
                Response.[End]()

           Catch ex As Exception
            Finally
                GridView1.Columns(0).Visible = True
            End Try
        End If
    End Sub

Thanks so much

Answer 13

User1535942433 posted

Hi E.RU,

How do you write your .xml file. Since you don't post your files,I have created a test and it works fine.

Just like this:

  Protected Sub Button1_Click(sender As Object, e As EventArgs)
        Dim FileUpload1 As FileUpload = TryCast(FindControl("FileUpload1"), FileUpload)

        If FileUpload1.HasFile And FileUpload1.FileContent IsNot Nothing Then

            Dim xmlFile As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

            Dim dataSet As DataSet = New DataSet()
            dataSet.ReadXml(xmlFile, XmlReadMode.InferSchema)

            For Each table As DataTable In dataSet.Tables
                Console.WriteLine(table)

                '--GETS HEADER
                For i As Integer = 0 To table.Columns.Count - 1
                    Console.Write(vbTab & table.Columns(i).ColumnName.Substring(0, Math.Min(20, table.Columns(i).ColumnName.Length)))
                    Dim header As String = (vbTab & table.Columns(i).ColumnName.Substring(0, Math.Min(20, table.Columns(i).ColumnName.Length)))

                Next
                Console.WriteLine() '--OUTPUTD HEADERS 

                For Each row In table.AsEnumerable()

                    For i As Integer = 0 To table.Columns.Count - 1
                        Console.Write(vbTab & row(i)) '--CONTENTS UNDER HEADER
                        Dim Data As String = vbTab & row(i)

                    Next

                    Console.WriteLine() '-- OUTPUTS HEADER CONTENTS 

                Next
                GridView1.DataSource = dataSet
                GridView1.DataBind()

            Next

        End If
    End Sub

XML file:

<Item>
<Item1>
        <Number>1</Number>
        <Code>123</Code>
        <Description>AB</Description>
        <Amount Name="Invoice">50.00</Amount>
        <Amount Name="Paid">10.00</Amount>
        <Product>
            <ProductName>Product57</ProductName>
                <ProductAmount>40</ProductAmount>
        </Product>
        <TotalAmount>763.860</TotalAmount>
        <ID>2</ID>
</Item1>
<Item1>
        <Number>12</Number>
        <Code>12rewre3</Code>
        <Description>ArB</Description>
        <Amount Name="Invoice">50.00</Amount>
        <Amount Name="Paid">10.00</Amount>
        <Product>
            <ProductName>Product57</ProductName>
                <ProductAmount>40</ProductAmount>
        </Product>
        <TotalAmount>7343.860</TotalAmount>
        <ID>5</ID>
</Item1>
</Item>

By the way,why you don't bind gridview directly? Just like this:

 Protected Sub Button1_Click(sender As Object, e As EventArgs)
        Dim FileUpload1 As FileUpload = TryCast(FindControl("FileUpload1"), FileUpload)

        If FileUpload1.HasFile And FileUpload1.FileContent IsNot Nothing Then

            Dim xmlFile As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

            Dim dataSet As DataSet = New DataSet()
            dataSet.ReadXml(xmlFile, XmlReadMode.InferSchema)

            GridView1.DataSource = dataSet
            GridView1.DataBind()

        End If
    End Sub

Best regards,

Yijing Sun

Answer 14

User1717218719 posted

Hi Yijing Sun,

My xml file looks similar to this layout below:

<?xml version="1.0" encoding="us-ascii"?>
<Invoice>
<productlist>
    <Version>V4.2.0.1</Version>
    <ID>dedab33</ID>
    <Category>4</Category>
</productlist>
<Item>
<Item>
        <Number>1</Number>
        <Code>123</Code>
        <Description>AB</Description>
        <Amount Name="Invoice">50.00</Amount>
        <Amount Name="Paid">10.00</Amount>
        <Product>
            <ProductName>Product57</ProductName>
                <ProductAmount>40</ProductAmount>
        </Product>
        <TotalAmount>763.860</TotalAmount>
        <ID>2</ID>
</Item>
<Item>
        <Number>12</Number>
        <Code>12rewre3</Code>
        <Description>ArB</Description>
        <Amount Name="Invoice">50.00</Amount>
        <Amount Name="Paid">10.00</Amount>
        <Product>
            <ProductName>Product57</ProductName>
                <ProductAmount>40</ProductAmount>
        </Product>
        <TotalAmount>7343.860</TotalAmount>
        <ID>5</ID>
</Item>
<productlistsummary>
    <Count>2</Count>
    <Total>10.18</Amount>
</productlistsummary>
</Invoice>

Answer 15

User1535942433 posted

Hi E.RU,

According to your description and codes,you have 5 datatables and the dataset fills the first datatable. 

According to your purpose,I think you need to create multiple datatables and  for loop that iterated through each table in the dataset and merging it. Or you could create many gridviews. Just like this:

https://docs.microsoft.com/de-de/dotnet/api/system.data.datatable.merge?redirectedfrom=MSDN&view=net-5.0#System_Data_DataTable_Merge_System_Data_DataTable_

nested gridview:

<?xml version="1.0" encoding="utf-8" ?>
<ApiResponse>
  <Errors>
    <ProfileId>grghhfh</ProfileId>
    <message>Authentication </message>
    <response>403</response>
    <detail>
      <Detail>
        <message>Authentication</message>
        <response>403</response>
      </Detail>
    </detail>
  </Errors>
</ApiResponse>

  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" 
                OnRowDataBound="GridView1_RowDataBound" DataKeyName="ProfileId">
                <Columns>
                    <asp:BoundField DataField="ProfileId" HeaderText="ProfileId" />
                    <asp:BoundField DataField="message" HeaderText="message" />
                    <asp:BoundField DataField="response" HeaderText="response" />
                    <asp:TemplateField HeaderText="Detail">
                        <ItemTemplate>
                            <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false">
                                <Columns>
                                    <asp:BoundField DataField="message" HeaderText="message" />
                                    <asp:BoundField DataField="response" HeaderText="response" />

                                </Columns>
                            </asp:GridView>
                        </ItemTemplate>
                    </asp:TemplateField>

                </Columns>
            </asp:GridView>

Class SurroundingClass
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        doc2 = New XmlDocument()
        doc2.Load(Server.MapPath("Error.xml"))
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn() {New DataColumn("ProfileId"), New DataColumn("message"), New DataColumn("response")})
        Dim root1 As XmlElement = doc2.DocumentElement
        Dim nodes1 As XmlNodeList = root1.SelectNodes("Errors")

        For Each node As XmlNode In nodes1
            dt.Rows.Add(node.SelectNodes("ProfileId")(0).InnerText, node.SelectNodes("message")(0).InnerText, node.SelectNodes("response")(0).InnerText)
        Next

        GridView1.DataSource = dt
        GridView1.DataBind()
    End Sub

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim gv2 As GridView = CType(e.Row.Cells(3).FindControl("GridView2"), GridView)
            Dim ProfileId As String = e.Row.Cells(0).Text.ToString()
            Dim root As XmlElement = doc2.DocumentElement
            Dim nodes As XmlNodeList = root.SelectNodes("Errors")

            For Each node As XmlNode In nodes

                If node.SelectNodes("ProfileId")(0).InnerText = ProfileId Then
                    Dim dt1 As DataTable = New DataTable()
                    dt1.Columns.AddRange(New DataColumn() {New DataColumn("message"), New DataColumn("response")})
                    Dim nodelist2 As XmlNodeList = node.SelectNodes("detail")(0).SelectNodes("Detail")(0).ChildNodes
                    dt1.Rows.Add(nodelist2(0).InnerText, nodelist2(1).InnerText)
                    gv2.DataSource = dt1
                    gv2.DataBind()
                End If
            Next
        End If
    End Sub
End Class

Best regards,

Yijing Sun

Answer 16

User1717218719 posted

Thank you for your reply.

Unfortunatly tis is not an ideal solution for me as my xml file is ver large and can vary from time to time as I want to be able to upload different xml files not just one. it would be very time consuming for me to have to change the griview boundfield datafields.(apologise I should have mentioned that before now to make things clearer).

I am a little confused as to why when I run the code it outputs to the console perfectly all the information but when I bind it to te gridview I only get the first line of data.

Is there a way to used the console information to export it to excl or an alternatve way to bind ?

Thanks so much !

Answer 17

User1535942433 posted

Hi E.RU,

Is there a way to used the console information to export it to excl or an alternatve way to bind ?

I think it's meaningless. I recommend you could each datatable bind each gridview and you could use nested gridview to show each nodes.

Best regards,

Yijing Sun