[RESOLVED] Help with XML nested elements

[Teslaza]

Hi
I'm not really sure if it is a something simple that I am just not grasping or something which might require a different approach.
I am trying to achieve the following result below (I have removed a lot of the meta data to aid with reading):

<dealer>
<dealername>ABC</dealername>
<dealerID>1</dealerID>
<vehicle>
<stockID>100</stockID>
<make>Samsung</make>
<model>Galaxy</model>
<images>
<imgurl>http://www.dealer-website.co.za/images/image1.jpg</imgurl>
<imgurl>http://www.dealer-website.co.za/images/image2.jpg</imgurl>
</images>
</vehicle>
….
</dealer>

My problem lies with repeating <imgurl> fields inside the images tag WITHOUT the numbers .

Currently I have only been able to produce the following 2 Results :

Result 1 (tableImagesA) :

<dealer>
<dealerID>1</dealerID>
<dealername>ABC</dealername>
<vehicle>
<stockID>100</stockID>
<make>Samsung</make>
<model>Galaxy</model>
<images>
<image1>http://www.dealer-website.co.za/images/image1.jpg</image1>;
<image2>http://www.dealer-website.co.za/images/image2.jpg</image2>;
</images>
</vehicle>
….
</dealer>

And Result 2 (tableImagesB) :

<dealer>
<dealerID>1</dealerID>
<dealername>ABC</dealername>
<vehicle>
<stockID>100</stockID>
<make>Samsung</make>
<model>Galaxy</model>
<images>
<imgurl>http://www.dealer-website.co.za/images/image1.jpg</imgurl>;
</images>
<images>
<imgurl>http://www.dealer-website.co.za/images/image2.jpg</imgurl>;
</images>
</vehicle>
….
</dealer>


Code Section (I use tables from a database but have manually added the data to datatables below for clarity):

  Dim ds As New DataSet
 
        'Define Dealer table columns and types
        Dim tableDealers As New DataTable
        tableDealers.TableName = "dealer"
        tableDealers.Columns.Add(New DataColumn("dealerID", Type.GetType("System.Int32")))
        tableDealers.Columns.Add(New DataColumn("dealername", Type.GetType("System.String")))
 
        'Add Dealer data to table
        Dim obj As Object() = {"1", "ABC"} 'Create object array with row values
        tableDealers.Rows.Add(obj) 'Add row to table
 
        'Add table to dataset
        ds.Tables.Add(tableDealers)
 
        Dim tableVehicles As New DataTable
        tableVehicles.TableName = "vehicle"
        tableVehicles.Columns.Add(New DataColumn("stockID", Type.GetType("System.Int32")))
        tableVehicles.Columns.Add(New DataColumn("make", Type.GetType("System.String")))
        tableVehicles.Columns.Add(New DataColumn("model", Type.GetType("System.String")))
        tableVehicles.Columns.Add(New DataColumn("dealerID", Type.GetType("System.Int32")))
 
        obj = New Object() {"100", "Samsung", "Galaxy", "1"} 'New Object() for VS 2008 compatibility
        tableVehicles.Rows.Add(obj)
        obj = New Object() {"101", "LG", "Optimus", "1"}
        tableVehicles.Rows.Add(obj)
        obj = New Object() {"102", "Panasonic", "Lumix", "1"}
        tableVehicles.Rows.Add(obj)
        obj = New Object() {"103", "Sony", "Xperia", "1"}
        tableVehicles.Rows.Add(obj)
 
        ds.Tables.Add(tableVehicles)
 
        Dim relDealerStock As DataRelation = New DataRelation("relDealerStock", tableDealers.Columns("dealerID"), tableVehicles.Columns("dealerID"))
        relDealerStock.Nested = True
        tableVehicles.Columns("dealerID").ColumnMapping = MappingType.Hidden
        ds.Relations.Add(relDealerStock)
 
        'tableImagesA - representation of what the actual table design looks like in the database
        Dim tableImages As New DataTable
        tableImages.TableName = "images"
        tableImages.Columns.Add(New DataColumn("stockID", Type.GetType("System.Int32")))
        tableImages.Columns.Add(New DataColumn("image1", Type.GetType("System.String")))
        tableImages.Columns.Add(New DataColumn("image2", Type.GetType("System.String")))
        tableImages.Columns.Add(New DataColumn("image3", Type.GetType("System.String")))
        tableImages.Columns.Add(New DataColumn("image4", Type.GetType("System.String")))
        tableImages.Columns.Add(New DataColumn("image5", Type.GetType("System.String")))
 
        obj = New Object() {"100", "http://www.dealer-website.co.za/images/image1.jpg", "http://www.dealer-website.co.za/images/image2.jpg"}
        tableImages.Rows.Add(obj)
        obj = New Object() {"102", "http://www.dealer-website.co.za/images/image1b.jpg"}
        tableImages.Rows.Add(obj)
 
        'tableImagesB - representation of transposed version of the tableImagesA table by means of union queries
        'Dim tableImages As New DataTable
        'tableImages.TableName = "images"
        'tableImages.Columns.Add(New DataColumn("stockID", Type.GetType("System.Int32")))
        'tableImages.Columns.Add(New DataColumn("imgurl", Type.GetType("System.String")))
 
        'obj = New Object() {"100", "http://www.dealer-website.co.za/images/image1.jpg"}
        'tableImages.Rows.Add(obj)
        'obj = New Object() {"100", "http://www.dealer-website.co.za/images/image2.jpg"}
        'tableImages.Rows.Add(obj)
        'obj = New Object() {"102", "http://www.dealer-website.co.za/images/image1b.jpg"}
        'tableImages.Rows.Add(obj)
 
        ds.Tables.Add(tableImages)
 
        Dim relStockImages As DataRelation = New DataRelation("relStockImages", tableVehicles.Columns("stockID"), tableImages.Columns("stockID"))
        relStockImages.Nested = True
        tableImages.Columns("stockID").ColumnMapping = MappingType.Hidden
        ds.Relations.Add(relStockImages)
 
        ds.DataSetName = "root"
 
        Me.txtDisplay.Text = ds.GetXml

I’m not sure what to try further. I was hoping that someone might be able to shine some light on this matter.
Thank you

[techgnome]

Is the sole reason for the datatable so that you can get to the XML? If so, then yes, you need a different approach.
Your best bet would be to use XMLDocument and add nodes as needed.

There is also a way using XML Literals to create the xml "template" that you can then run through and it'll populate it with the data... not sure how it's done exactly but I'm sure if you search the forums for "xml literals" you'll find examples. I've seen it done a number of times.

-tg

[kareninstructor]

Simple xml literal demo

Dim dt As New DataTable With {.TableName = "MyTable"}
dt.Columns.Add(
    New DataColumn With
    {
        .ColumnName = "Identifier",
        .DataType = GetType(Int32),
        .AutoIncrement = True,
        .AutoIncrementSeed = 1
    }
)
dt.Columns.Add(
    New DataColumn With
    {
        .ColumnName = "FirstName",
        .DataType = GetType(String)
    }
)
dt.Columns.Add(
    New DataColumn With
    {
        .ColumnName = "LastName",
        .DataType = GetType(String)
    }
)

dt.Rows.Add(New Object() {Nothing, "Joan", "Smith"})
dt.Rows.Add(New Object() {Nothing, "Mary", "Adams"})

Dim Data =
    <Employees>
        <%=
            From T In dt
            Select
            <Employee id=<%= T.Field(Of Integer)("Identifier") %>>
                <FirstName><%= T.Field(Of String)("FirstName") %></FirstName>
                <LastName><%= T.Field(Of String)("LastName") %></LastName>
            </Employee>
        %>
    </Employees>

Console.WriteLine(Data)

Results

<Employees>
  <Employee id="1">
    <FirstName>Joan</FirstName>
    <LastName>Smith</LastName>
  </Employee>
  <Employee id="2">
    <FirstName>Mary</FirstName>
    <LastName>Adams</LastName>
  </Employee>
</Employees>

Now let's read a file

<?xml version="1.0" encoding="utf-8" ?>
<dealer>
  <dealername>ABC</dealername>
  <dealerID>1</dealerID>
  <vehicle>
    <stockID>100</stockID>
    <make>Samsung</make>
    <model>Galaxy</model>
    <images>
      <imgurl>http://www.dealer-website.co.za/images/image1.jpg</imgurl>
      <imgurl>http://www.dealer-website.co.za/images/image2.jpg</imgurl>
    </images>
  </vehicle>
</dealer>

The code below has one field and one made up element

Dim ds As New DataSet
ds.ReadXml(IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "XmlFile1.xml"))
Dim dt As DataTable = ds.Tables("imgurl")
Dim Data =
    <Images>
        <%=
            From T In dt
            Select
            <image id=<%= IO.Path.GetFileName(T.Field(Of String)("imgurl_text")) %>>
                <Whatever><%= CInt(Math.Ceiling(Rnd() * 10)) %></Whatever>
            </image>
        %>
    </Images>

Console.WriteLine(Data)

Result

<Images>
  <image id="image1.jpg">
    <Whatever>8</Whatever>
  </image>
  <image id="image2.jpg">
    <Whatever>6</Whatever>
  </image>
</Images>

The following example is not a DataTable but still doable in a DataTable, instead I am using a Data Context class which represents a strong typed class for data retrieved from a SQL-Server database.

Private Sub DumpTordersToFile()
    Dim db As New DataClasses1DataContext
    Dim Result = (From C In db.Customers Where C.CustomerID = "1").FirstOrDefault

    Dim xmlOrders = _
    <orders>
        <%= From Customer In db.Customers _
            Where Customer.Country = "USA" _
            Select _
            <customer id=<%= Customer.CustomerID %>>
                <name><%= Customer.ContactName %></name>
                <address><%= Customer.Address %></address>
                <city><%= Customer.City %></city>
                <zip><%= Customer.PostalCode %></zip>
                <orders>
                    <%= From Order In Customer.Orders _
                        Select _
                        <order total=<%= _
                                         Aggregate Detail In Order.Order_Details _
                                         Into Sum(Detail.UnitPrice * Detail.Quantity) %>>
                            <date><%= Order.OrderDate %></date>
                            <details>
                                <%= From Detail In Order.Order_Details _
                                    Select _
                                    <product id=<%= Detail.ProductID %>>
                                        <name>
                                            <%= Detail.Product.ProductName %>
                                        </name>
                                    </product> %>
                            </details>
                        </order> %>
                </orders>
            </customer> %>
    </orders>

    xmlOrders.Save("Test.txt")
End Sub

[Teslaza]

Wow, great stuff. Thank you techgnome for the xml literal suggestion and kevininstructor for the great example with breakdown.