Create Nested XML from Stored Procedure

Imports System.Xml.Linq
Imports System.IO


' The list results holds the parsed values from the Results.txt file
Dim results As List(Of ResultInfo) = New List(Of ResultInfo)
' Read the text file and parse it
Using fileParser As New FileIO.TextFieldParser("results.txt")
    fileParser.TextFieldType = FileIO.FieldType.FixedWidth
    Dim Fieldwidth() As Integer = New Integer() {26, 76, 51, 255, 17, 17, -1}
    fileParser.SetFieldWidths(Fieldwidth)
    Dim currentRow As String()
    ' Read the two lineas of headder
    fileParser.ReadFields()
    fileParser.ReadFields()
    While Not fileParser.EndOfData
        currentRow = fileParser.ReadFields()
        Dim newRec As New ResultInfo
        newRec.Make = currentRow(0).Trim
        newRec.Model = currentRow(1).Trim
        newRec.Trim = currentRow(2).Trim
        newRec.Qty = Integer.Parse(currentRow(3).Trim)
        newRec.PriceFrom = Decimal.Parse(currentRow(4).Trim)
        newRec.PriceTo = Decimal.Parse(currentRow(5).Trim)
        newRec.Type = currentRow(6).Trim
        results.Add(newRec)
    End While
End Using

Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-8", "yes"), _
                            New XElement("Vehicles"))

Dim groupResults = From ri In results _
                   Group ri By ri.Make Into makeGroup = Group _
                   Select New With {.Make = makeGroup.First().Make, _
                                    .TotalQty = (From q In makeGroup _
                                                 Select q.Qty).Sum(), _
                                    .Models = From model In makeGroup _
                                              Group model By model.Model Into modelGroup = Group _
                                              Select New With {.Qty = modelGroup.Sum(Function(q As ResultInfo) q.Qty), _
                                                                .Modesls = modelGroup}}

For Each make In groupResults
    Dim mk As XElement = <make name=<%= make.Make %> qty=<%= make.TotalQty %>></make>
    For Each model In make.Models
        Dim md As XElement = <model name=<%= model.Modesls.First().Model %> qty=<%= model.Qty %>></model>
        For Each der As ResultInfo In model.Modesls
            Dim derivative As XElement = <derivative name=<%= der.Trim %> qty=<%= der.Qty %>
                                             priceFrom=<%= der.PriceFrom %> priceTo=<%= der.PriceTo %>></derivative>
            md.Add(derivative)
        Next
        mk.Add(md)
    Next
    xDoc.Root.Add(mk)
Next


Public Class ResultInfo
    Public Make As String
    Public Model As String
    Public Trim As String
    Public Qty As Integer
    Public PriceFrom As Decimal
    Public PriceTo As Decimal
    Public Type As String
End Class

Select allOpen in new window