Parse a complex xml document vb.net

Dim HDS = From x In xmlString.Root.Descendants("headings").Descendants("heading") Select x.Value

Select allOpen in new window

You did LOT more than what I asked for. (you did everything for me :-) )

Dim nHDS = (From w In xmlString.Root.Descendants("headings").Descendants("heading") Where IsNumeric(w.Attribute("for").Value) Select
           New With {.hName = w.Attribute("for").Value, .hValue = w.Value}).Distinct

Select allOpen in new window

Array.ForEach(nHDS.ToArray, Sub(x) HDataTable.Columns.Add(New DataColumn With {.ColumnName = x.hValue, .AllowDBNull = True}))

Select allOpen in new window

For Each d In nHDS
    Try
         HDataTable.Columns.Add(New DataColumn With {.ColumnName = d.hValue, .AllowDBNull = True})
    Catch ex As Exception
        'this will trap duplicate columns and still continue running
    End Try
Next

Select allOpen in new window

<heading for="result_status">Result Status</heading>
<heading for="date_start">Start Time</heading>
<heading for="date_finish">Finish Time</heading>
<heading for="date_update">Update Time</heading>
<heading for="user_ip">User's IP Address</heading>
<heading for="user_browser">User's Browser</heading>
<heading for="user_os">User's Operating System</heading>
<heading for="user_referrer">User's Referring URL</heading>
<heading for="payment_status">Payment Status</heading>
<heading for="payment_amount">Payment Amount Paid</heading>
<heading for="4">Fornavn</heading>
<heading for="5">Efternavn</heading>
<heading for="6">Adresse</heading>
<heading for="8">By</heading>
<heading for="10">Postnummer</heading>
<heading for="11">Primært telefonnummer</heading>
<heading for="37">Andet telefonnummer</heading>
<heading for="12">e-mail adresse</heading>
<heading for="21">Registreringsnummer</heading>
<heading for="23">Stelnummer</heading>
<heading for="22">Første registreringsdato</heading>
<heading for="24">Bilmærke</heading>
<heading for="25">Model</heading>
<heading for="28">Farve</heading>
<heading for="29">Forsikringsselskab</heading>
<heading for="43">Er køretøjet kaskoforsikret</heading>
<heading for="70">Kilometerstand</heading>
<heading for="17">Jeg har læst betingelserne for det bestilte abonnement.</heading>
<heading for="39">Hvor har du autohjælp idag</heading>
<heading for="44">Skal Redningskorps Danmark opsige Deres nuværende aftale</heading>
<heading for="69">Eventuelt nuværende abonnementsnummer</heading>
<heading for="30">Bemærkninger til min bestilling</heading>
<heading for="42">Rabatkode</heading>
<heading for="45">Ønsker De også et trailerabonnement til almindelig trailer (550 kg totalvægt) til 98 kroner</heading>
<heading for="60">Registreringsnummer</heading>
<heading for="61">Stelnummer</heading>
<heading for="62">Første registreringsdato</heading>
<heading for="63">Mærke</heading>
<heading for="64">Totalvægt op til</heading>
<heading for="65">Forsikringsselskab</heading>
<heading for="66">Er traileren kaskoforsikret</heading>
<heading for="67">Trailertype</heading>

Select allOpen in new window

Imports System.IO
Imports System.Net

Public Class Form1

    Dim request As HttpWebRequest
    Dim response As HttpWebResponse = Nothing
    Dim reader As StreamReader
    Dim xmlUrl As String = "https://urltoRESTAPI"

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            ' Create the web request  
            request = DirectCast(WebRequest.Create(xmlUrl), HttpWebRequest)

            ' Get response  
            response = DirectCast(request.GetResponse(), HttpWebResponse)

            ' Get the response stream into a reader  
            reader = New StreamReader(response.GetResponseStream())

            Dim xmlString As XDocument
            xmlString = XDocument.Load(reader)


            Dim HDS = From x In xmlString.Root.Descendants("headings").Descendants("heading")
                    Where Not IsNumeric(x.Attribute("for").Value) Select x.Attribute("for").Value
            Dim nHDS = From w In xmlString.Root.Descendants("headings").Descendants("heading") Where IsNumeric(w.Attribute("for").Value) Select
                       New With {.hName = w.Attribute("for").Value, .hValue = w.Value}

            'declare the datatable
            Dim HDataTable As DataTable = New DataTable

            'first add the transaction ID column
            HDataTable.Columns.Add(New DataColumn With {.ColumnName = "TranID", .AllowDBNull = False, .Unique = True})

            'then add all the normal columns, i.e no numeric attributes
            Array.ForEach(HDS.ToArray, Sub(x) HDataTable.Columns.Add(New DataColumn With {.ColumnName = x, .AllowDBNull = True}))

            'add the columns with numeric attributes
            Array.ForEach(nHDS.ToArray, Sub(x) HDataTable.Columns.Add(New DataColumn With {.ColumnName = x.hName, .AllowDBNull = True}))

            'here filter the results from the xml
            Dim Rests = From x In xmlString.Root.Descendants("results").Descendants("result") Select x
            'iterate through the results' sets
            For Each x In Rests
                'this is the transaction ID
                Dim mID = x.Attribute("id").Value
                'get the values of metas
                Dim Metas = (From q In x.Descendants("metas").Descendants("meta")
                             Select New With {.mName = q.Attribute("id").Value, .mValue = q.Value})
                'declare the datarow and start populating it
                Dim tCol As DataRow = HDataTable.NewRow()
                tCol.Item("TranID") = mID
                'this withthe metas
                Array.ForEach(Metas.ToArray, Sub(y) tCol.Item(y.mName) = y.mValue)
                'here try and resolve the headers with numbered attributes
                Dim Items = (From q In x.Descendants("items").Descendants("item")
                             Select New With {.mID = q.Attribute("id").Value, .mValue = q.Descendants("value").Value})
                For Each u In Items
                    Dim j = (From c In nHDS Where c.hName = u.mID Select c).FirstOrDefault
                    tCol.Item(j.hName) = u.mValue
                Next
                'finally add the row to the table
                HDataTable.Rows.Add(tCol)
                Label1.Text = HDataTable.Rows.Count
            Next
            'I added this to demonstrate, you'll need to add a datagridview to your form to use it
            Me.DataGridView1.DataSource = HDataTable
        Finally
            If Not response Is Nothing Then response.Close()
        End Try
    End Sub
End Class

Select allOpen in new window

Dim HDS = (From x In xmlString.Root.Descendants("headings").Descendants("heading") Select x.Attribute("for").Value).ToList
Dim HDataTable As DataTable = New DataTable
HDataTable.Columns.Add(New DataColumn With {.ColumnName = "TranID", .AllowDBNull = False, .Unique = True})
Array.ForEach(HDS.ToArray, Sub(x) HDataTable.Columns.Add(New DataColumn With {.ColumnName = x, .AllowDBNull = True}))
Dim Rests = (From x In xmlString.Root.Descendants("results").Descendants("result") Select x).ToList
For Each x In Rests
    Dim mID = x.Attribute("id").Value
    Dim Metas = (From q In x.Descendants("metas").Descendants("meta") Select New With {.mName = q.Attribute("id").Value, .mValue = q.Value}).ToArray
    Dim tCol As DataRow = HDataTable.NewRow()
    tCol.Item("TranID") = mID
    Array.ForEach(Metas, Sub(y) tCol.Item(y.mName) = y.mValue)
    Dim Items = (From q In x.Descendants("items").Descendants("item") Select New With {.mID = q.Attribute("id").Value, .mValue = q.Descendants("value").Value}).ToArray
    Array.ForEach(Items, Sub(y) tCol.Item(y.mID) = y.mValue)
    HDataTable.Rows.Add(tCol)
Next
Me.DataGridView1.DataSource = HDataTable

Select allOpen in new window

Dim CountResults = (From x In xmlString.Root.Descendants("results").Descendants("result") Select x).Count

Select allOpen in new window