XML in foreach loop from dataset in C#

Question

I have to create a very specific shape XML file dynamically within C# to power a flash object on a website. The problem I have faced with many of my attempts is that most output wants each node to have some way of unique identification, I do not want that. Rather the below is the output I am going after, not the output I currently can get. Note it is also invalid XML.

<data>
    <Annual Enrollment>
        <row>
            <column>value1</column>
            <column>value2</column>
            <column>value3</column>
        </row>
        <row>
            <column>value1</column>
            <column>value2</column>
            <column>value3</column>
        </row>
    </Annual Enrollment>
    <Pre-College>
        <row>
            <column>value1</column>
            <column>value2</column>
            <column>value3</column>
        </row>
        <row>
            <column>value1</column>
            <column>value2</column>
            <column>value3</column>
        </row>
    </Pre-College>

....AND so forth. The node titles or and cannot change, nor can the roots for each tree.

The code I have so far looks like this, in my head it seems like it should work, however it does not.

  var tableResult = DashboardData.GetMetricData(1);
  // Outlinining structure
  XDocument document = new XDocument(
       new XDeclaration("1.0", "utf-8", null),
       new XElement("data",
            new XElement("AnnualEnrollment"),
            new XElement("Pre-College"),
            new XElement("Summary")
            ));
  // Now I need to append children to each of the three nodes within the root "data"
  foreach (DataRow row in tableResult.Tables[0].Rows)
  {
      document.Element("AnnualEnrollment").Add(new XElement("row"));

      foreach (var item in row.ItemArray)
      {
          var element = new XElement("column", item);


      }
  }

Which bit isn't working? If the output is not what you are expecting, could you update your question with what it looks like? — nick_w, Oct 31 '12 at 22:57
I updated text a bit. A lot of the issue comes from the fact that it is invalid XML in the first place. .Net doesn't want you to create invalid XML. I am starting to think I may have to just use a streamwriter and write tree by tree and only save as .xml. — JCanlett, Oct 31 '12 at 23:13
Have you considered doing token replacement to insert the values? It's kind of a hack in comparison to an XML library but I get the feeling none of those will work anyways since your XML is invalid. — evanmcdonnal, Oct 31 '12 at 23:20

Lukas Winzenried · Answer 1 · 2012-11-01T00:06:28.863

3

Consider using XmlWriter to gain more control and flexbilily about document structure

var docBuilder = new StringBuilder();
using (var writer = XmlWriter.Create(docBuilder))
{
    writer.WriteStartElement("data");
    writer.WriteStartElement("AnnualEnrollment");
    foreach (var row in dataTable.Rows)
    {
        writer.WriteStartElement("row");
        foreach (var item in row.ItemArray)
            writer.WriteElementString("column", item);
        writer.WriteEndElement(); // row
    }
    writer.WriteEndElement(); // Annual Enrollment
    writer.WriteEndElement(); // data
}
docBuilder.Replace("<AnnualEnrollment>", "<Annual Enrollment>");

edited Nov 01 '12 at 00:06

answered Oct 31 '12 at 23:27

Lukas Winzenried

1,919
1
14
22

Lukas! Awesome idea. I think that would be perfect as logically that is how the values will be written anyway, sequentially. Basically calling 19 Stored procedures and creating one large datasource for XCelsius objects. XmlWriter will allow me to do it exactly the way I wanted. – JCanlett Oct 31 '12 at 23:52
And yes, I am required to write invalid XML, the fault of the tool used, not the developer! =) – JCanlett Oct 31 '12 at 23:53
good old foreach ;-) but I think the main problem is not solved. As I understand, user1789915 requires the space in between "Annual Enrollment" which is an illegal element name. – Lukas Winzenried Oct 31 '12 at 23:54
to get the space I wold consider to construct the document as valid xml and finalize with doc.replace("AnnualEnrollment", "Annual Enrollment") – Lukas Winzenried Oct 31 '12 at 23:58
Yes you are right. So I may still be writing a text document with a lot of XML like elements inside it. =O) – JCanlett Oct 31 '12 at 23:58
1

@lazyberezovsky Copy paste? I don't see any copy pasting going on here, I just see a valid answer to the problem, and I saw it up before anyone else's. – ean5533 Nov 01 '12 at 00:48
1

@lazyberezovsky ...you really think he copied that from you? It doesn't take a genius to come up with the idea of using `string.Replace`. I think you're taking this stackoverflow thing a bit too seriously. – ean5533 Nov 01 '12 at 00:56
@ean5533 I think everybody could come up with that idea, but I usually give a link for one who did it first. If you don't - it's up to you – Sergey Berezovskiy Nov 01 '12 at 01:16

score 1 · Answer 2 · answered Oct 31 '12 at 23:20

I would expect it to look more like this:

  foreach (DataRow row in tableResult.Tables[0].Rows)
  {
     XElement aRow = new XElement("row")

     foreach (var item in row.ItemArray)
     {
          aRow.Add(new XElement("column", item))
     }

     document.Element("AnnualEnrollment").Add(aRow);

  }

Sergey Berezovskiy · Accepted Answer · 2012-10-31T23:49:14.853

Sample below reads rows from first table and converts them to row elements, providing as content columns values converted to column elements.

XDocument document = new XDocument(
    new XDeclaration("1.0", "utf-8", null),
    new XElement("data",
        new XElement("AnnualEnrollment", 
            from row in tableResult.Tables[0].AsEnumerable()
            select new XElement("row", 
                from column in tableResult.Tables[0].Columns.Cast<DataColumn>()
                select new XElement("column", row[column]))),
        new XElement("Pre-College"), // same for pre-college table
        new XElement("Summary") // and same for summary
        ));

Also I'd extracted DataTable conversion into separate (extension) method:

public static object ToXml(this DataTable dataTable)
{
    return from row in dataTable.AsEnumerable()
           select new XElement("row",
                      from column in dataTable.Columns.Cast<DataColumn>()
                      select new XElement("column", row[column]));
}

Now your Xml generation will look like:

XDocument document = new XDocument(
    new XDeclaration("1.0", "utf-8", null),
    new XElement("data",
        new XElement("AnnualEnrollment", tableResult.Tables[0].ToXml()),
        new XElement("Pre-College", tableResult.Tables[1].ToXml()),
        new XElement("Summary", tableResult.Tables[2].ToXml()) 
        ));

If you really need space between `AnnualEnrollment`, then simply do `document.ToString().Replace("AnnualEnrollment", "Annual Enrollment")` — Sergey Berezovskiy, Oct 31 '12 at 23:57

XML in foreach loop from dataset in C#

3 Answers3