[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

FAIL READING XML INTO DATATABLE

Posted on 2011-03-09
11
Medium Priority
?
504 Views
Last Modified: 2012-06-27
Hi All,

I have a xml table and I want to load it into datatable, but failed to do so.

What's wrong ?

Thank you.
Imports System
Imports System.Data
Imports System.Data.SqlClient

Imports System.IO

Public Class frmReadXML

    Private Sub Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try

            Dim strSourceFromClientFolder As String = System.AppDomain.CurrentDomain.BaseDirectory()
            Dim strXMLFileName As String = "121110110334.xml"

            ' Read in XML from file
            Dim dtTable As DataTable = Me.Read_XML_To_DataTable(strSourceFromClientFolder, strXMLFileName)

            ' Bind DataSet to Data Grid
            dgvData.DataMember = "Data"
            dgvData.DataSource = dtTable

        Catch ex As Exception
            MsgBox("Gagal Buka XML File ...!", MsgBoxStyle.Information, Me.Text)
        End Try

    End Sub

    Private Function Read_XML_To_DataTable(ByVal strFolderFile As String, _
                                                 ByVal strXMLFileName As String) As DataTable

        Try

            Dim dsTable As New DataSet
            Dim fs As FileStream

            fs = New FileStream(strFolderFile & strXMLFileName, FileMode.Open)

            dsTable.ReadXml(fs)

            Return dsTable.Tables(0)

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

        Return Nothing

    End Function

Open in new window

121110110334.xml
0
Comment
Question by:emi_sastra
  • 7
  • 4
11 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 35093218
>> I have a xml table and I want to load it into datatable, but failed to do so.

Do you have any error ? I just tried it here, and it loads into several tables...
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35099955
Hi Dhaest,

Please see the result.

Thank you.
READ-XML.PNG
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35099962
How could load those tables to datagridview?

Thank you.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 53

Expert Comment

by:Dhaest
ID: 35107248
That's very hard, because you have a multilevel xml-file. It will be split into several datatables.

What would you like to see in your datagrid ?
From where does your xml come from ? Is it coming from a database, why don't you query the database ?

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35107300
- That's very hard, because you have a multilevel xml-file. It will be split into several datatables.
Could we use 2 datagridview, one for tables and one for its table data.

- What would you like to see in your datagrid ?
The data there. We want to construct it.

- From where does your xml come from ? Is it coming from a database, why don't you query the database ?
It is exported from firebird, we can not direct access to it, since the vendor not allowed it.

Thank you.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 35107347
>>  Could we use 2 datagridview, one for tables and one for its table data.

Yes you can fe load all the tablenames into a combobox and depending of what's selected, show this into the datagrid. But it all has keys (foreign keys), so I'm afraid that it's not very usefull information (in some tables)
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35107359
-Yes you can fe load all the tablenames into a combobox and depending of what's selected, show this into the datagrid. But it all has keys (foreign keys), so I'm afraid that it's not very usefull information (in some tables)
Never mind. If it is the case, I just curious how to load it.

Thank you.
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 2000 total points
ID: 35107409
What you also can do is to create a linq-query to retrieve all the information back into one structure.

A small example below
Dim InvoiceAdd As DataTable = dsTable.Tables("InvoiceAdd")
            Dim CustomerRef As DataTable = dsTable.Tables("CustomerRef")

            Dim query = _
                InvoiceAdd.AsEnumerable().Join(CustomerRef.AsEnumerable(), _
                Function(Invoice) Invoice.Field(Of Int32?)("InvoiceAdd_ID"), _
                Function(Customer) Customer.Field(Of Int32?)("InvoiceAdd_ID"), _
                    Function(InvoiceA, CustomerR) New With _
                    { _
                        .InvoiceAdd_ID = InvoiceA.Field(Of Int32?)("InvoiceAdd_ID"), _
                        .SequenceNo = InvoiceA.Field(Of String)("SequenceNo"), _
                        .TxnDate = InvoiceA.Field(Of String)("TxnDate"), _
                        .FiscalRate = InvoiceA.Field(Of String)("FiscalRate"), _
                        .Rate = InvoiceA.Field(Of String)("Rate"), _
                        .Description = InvoiceA.Field(Of String)("Description"), _
                        .CustomerRef_ListNo = CustomerR.Field(Of String)("ListNo"), _
                        .CustomerRef_FullName = CustomerR.Field(Of String)("FullName") _
                    })

            For Each group In query

            Next

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35107473
Thank you for the code above.

What I am struggling now is how to load the xml file to dataset.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35142684
Hi Dhaest,

Any solution ?

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35157248
Hi Dhaest,

I have succeed read xml to dataset and field dataset table names to datagridview (dgvTables).

How I could load data to datagridview (dgvData) when user click dgvTables using the xml data ?

Thank you.
         

dsTables.ReadXml(strXMLFileName)

            dgvTables.Columns.Add("Table", "Table")

            For Each tbl As DataTable In dsTables.Tables
                Dim dgvRow As New DataGridViewRow
                Dim dgvCell As DataGridViewCell

                dgvCell = New DataGridViewTextBoxCell()
                dgvCell.Value = tbl.TableName
                dgvRow.Cells.Add(dgvCell)

                dgvTables.Rows.Add(dgvRow)
            Next

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Jorge
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Suggested Courses
Course of the Month7 days, 17 hours left to enroll

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question