Need help converting DataTable to XML VB.NET

Question

I need help converting DataTable to XML. I have done it using LINQ but I can't get exactly as I have to. I made a picture so that you can easily understand. The XML needs to be in certain format like below so I can't just use dt.writexml(). Artist ID needs to auto number. Songs are groupped by Artist. Prefer a solution in Linq coz that's what I have used throughout the project but I coundn't manage to get what I want here. The columns names are known so you can use something like this in the code. row.Field(Of String)("title")

Thanks a lot . I mean it. Sorry for poor english.

enter image description here

CreateDatatable - this simple code should create a datatable

            Dim dTable As New DataTable
            dTable.Columns.Add("Title")
            dTable.Columns.Add("Artist")
            dTable.Columns.Add("Album")

            dTable.Rows.Add("Baby one more time", "Britney Spears", "Baby one more time")
            dTable.Rows.Add("Crazy", "Britney Spears", "Best of")
            dTable.Rows.Add("Every time", "Britney Spears", "Best of")
            dTable.Rows.Add("Black and White", "Michael Jackson", "Best of")
            dTable.Rows.Add("You are not alone", "Michael Jackson", "Best of")
            dTable.Rows.Add("Smile", "Michael Jackson", "Best of")

what I have at the moment. It will convert it datatable to xml without the groupping and album index.

      Dim xmlDoc As New XDocument(
      From row In dt.Rows
      Select XElement("SONG",
      From column In dt.Columns
            Select
                New XAttribute(column.Name, row.Item(column.Name))
             )
      )

well .. i also have some more code .. that will query first created xml and do the grouping but still having album="albumname" in the song element as attribute. And it should be just one query from datatable to xml .. i hate having to query against xml again to just refomat it.

    Dim replacement = New XDocument(New XElement("root", 
    original.Descendants("Song")
    .GroupBy(Function(x) Convert.ToString(x.Element("artist").value))
    .[Select](Function(songsForArtist, index) 
     New XElement("artist", New XAttribute("id", index + 1),
     New XAttribute("name", songsForArtist.Key), songsForArtist))))

Laszlo Boke · Accepted Answer

I hope you can convert it to VB.NET

    using System;
    using System.Linq;
    using System.Data;
    using System.Xml.Linq;

    namespace ConsoleApplication3
    {
        class Program
        {
            static void Main(string[] args)
            {
                var dTable = new DataTable();
                dTable.Columns.Add("Title");
                dTable.Columns.Add("Artist");
                dTable.Columns.Add("Album");

                dTable.Rows.Add("Baby one more time", "Britney Spears", "Baby one more time");
                dTable.Rows.Add("Crazy", "Britney Spears", "Best of");
                dTable.Rows.Add("Every time", "Britney Spears", "Best of");
                dTable.Rows.Add("Black and White", "Michael Jackson", "Best of");
                dTable.Rows.Add("You are not alone", "Michael Jackson", "Best of");
                dTable.Rows.Add("Smile", "Michael Jackson", "Best of");

                var query = dTable.AsEnumerable().
                    GroupBy(row => row.Field("Artist")).
                    Select(
                        (grp, i) => new XElement("Artist",
                            new XAttribute("ID", i + 1),
                            new XAttribute("ARTISTNAME", grp.Key),
                                grp.Select(song => new XElement("SONG",
                                    new XAttribute("artistID", i + 1),
                                    new XAttribute("title", song.Field("Title")),
                                    new XAttribute("album", song.Field("Album"))
                                    )
                                )
                        )
                    );

                var xml = new XElement("Music", query);
            }
        }
    }

Need help converting DataTable to XML VB.NET

Answers (2)

Related Questions