XML to DataTable/DataGrid

Anoop Brijmohun

Hi All, I am trying to get specific values from the xml below, however i cannot get the desired result. the xml structure is as below..

<Orders>
<OrderHeader>
<CustomerPoNumber>TEST1</CustomerPoNumber>
<ErrorMessages>
<ErrorDescription>Invalid Po 'TEST1'</ErrorDescription>
</ErrorMessages>
</OrderHeader>
<OrderDetails>
<Line>0001</Line>
<ErrorMessages>
<ErrorDescription>'EAE' invalid</ErrorDescription>
</ErrorMessages>
<ErrorMessages>
<ErrorDescription>'EAS' invalid</ErrorDescription>
</ErrorMessages>
<Line>0002</Line>
<ErrorMessages>
<ErrorDescription>Price invalid</ErrorDescription>
</ErrorMessages>
</OrderDetails>
</Orders>

Shahan Ayyub

Hi! Can you please provide few more sample(s) ? I have just created a method that do the job. I need to test it. It would be be great if it may have as many cases as you have.

Simon_Whale

If you are reading the XML from file have a look at Dataset.ReadXML(filename)[^]

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Anoop Brijmohun

Hi , thanks for your help... there was a slight typo error in the xml structure... it should have reflected as below

<Orders>
<OrderHeader>
<CustomerPoNumber>TEST1</CustomerPoNumber>
<ErrorMessages>
<ErrorDescription>Invalid Po 'TEST1'</ErrorDescription>
</ErrorMessages>
</OrderHeader>
<OrderDetails>
<Stock>
<Line>0001</Line>
<ErrorMessages>
<ErrorDescription>'EAE' invalid</ErrorDescription>
<ErrorDescription>'EAS' invalid</ErrorDescription>
</ErrorMessages>
</Stock>
<Stock>
<Line>0002</Line>
<ErrorMessages>
<ErrorDescription>Price invalid</ErrorDescription>
</ErrorMessages>
</Stock>
</OrderDetails>
</Orders>

i have managed to come up with a solution that works for now even though it's very messy... have a look...

Private Function GetErrors(ByVal XML As String, ByVal Path As String) As DataTable
    Try
        Dim DOC As New Xml.XmlDocument
        DOC.LoadXml(XML)

        Dim ndParent As XmlNodeList
       'Path = //Orders
        ndParent = DOC.SelectNodes(Path)

        Dim nd As XmlNode
        Dim ndC1 As XmlNode
        Dim ndC2 As XmlNodeList
        Dim nd1 As XmlNode
        Dim nd2 As XmlNode
        Dim out As String = ""

        
        Dim dt As New DataTable
        dt.Columns.Add("PoNum")
        dt.Columns.Add("So")
        dt.Columns.Add("Line")
        dt.Columns.Add("Error")
        dt.Columns.Add("ErrHdr")
        Dim row As DataRow

        Dim PoNum As String = ""
        Dim Line As Integer = 0
        Dim errmsg As String = ""
        Dim errhdr As String = ""

        For Each nd In ndParent
            'below reads the first level i.e. order header + order details
            For Each ndC1 In nd.ChildNodes
                Select Case ndC1.Name.Trim
                    Case "OrderHeader"
                       'get the childnodes of OrderHeader
                       'xml result should search for FieldName i.e. "CustomerPoNumber"
                        ndC2 = ndC1.ChildNodes
                        PoNum = XmlResult(ndC2, "CustomerPoNumber")
                        errhdr = XmlResult(ndC2, "ErrorMessages")

Shahan Ayyub

I have optimized the code a bit more, kindly check it out:

Public Function XmlToDataTable(ByVal path As String) As DataTable
Dim dt As New DataTable
Dim doc As New XmlDocument()
Dim PoNum As String = String.Empty
Dim Header As String = String.Empty
doc.Load(path)
dt.Columns.Add("PoNum")
dt.Columns.Add("Header")
dt.Columns.Add("Line")
dt.Columns.Add("Error")
Dim cnt As Integer = doc.SelectNodes("Orders/OrderHeader").Count
For k As Integer = 0 To cnt - 1
Dim node As XmlNode = doc.SelectNodes("Orders/OrderHeader").Item(k)
PoNum = node.Item("CustomerPoNumber").InnerText
Header = node.Item("ErrorMessages").InnerText
Dim n As Integer = doc.SelectNodes("Orders/OrderDetails").Count
For m As Integer = 0 To n - 1
Dim element As XmlNode = doc.SelectNodes("Orders/OrderDetails").Item(m)
Dim obj(3) As Object
obj(0) = PoNum : obj(1) = Header
For Each stocks As XmlNode In element.ChildNodes
Dim line As String = stocks.Item("Line").InnerText
Dim description As String = stocks.Item("ErrorMessages").InnerXml.Replace("", " & ").Replace("", "").Replace("", "")
obj(2) = line : obj(3) = description
dt.Rows.Add(obj)
Next
Next
Next
Return dt
End Function

Anoop Brijmohun

Hi thanks, will try this out nd get back to you. thanks for your help... :)