A tough table transforming into XML

Question

I have a DataTable which I select from database(Well,these data cross several tables,after the query and putting into a DataTable,it shows at below)

ColumnA ColumnB
a             11
b             33
b             44
a             22
b             55

but I want to transform it into an XML like this

<root>
   <header name ='a'>
       <item name='11' />
       <item name='22' />
   </header>
   <header name ='b'>
       <item name='33' />
       <item name='44' />
       <item name='55' />
   </header>

</root>

Is there an easy way to implement it by C#?

Answer 1

Why bother with C# ?? You can do it in T-SQL directly (SQL Server 2005 and up):

SELECT 
    ColumnA AS '@name',
    (SELECT ColumnB AS '@name' 
     FROM YourTable t 
     WHERE t.ColumnA = YourTable.ColumnA 
     FOR XML PATH('item'), TYPE)
FROM 
    YourTable
GROUP BY
    ColumnA
FOR XML PATH('header'), ROOT('root')

Gives you:

<root>
  <header name="a">
    <item name="11" />
    <item name="22" />
  </header>
  <header name="b">
    <item name="33" />
    <item name="44" />
    <item name="55" />
  </header>
</root>

You can execute this SQL query using standard ADO.NET SqlCommand and get back the XML nicely formatted already.

Marc

Answer 2

This will do it using .NET 3.5 and the XDocument

XDocument yourDocument = new XDocument(new XElement("root",
    new XElement("header",
     new XAttribute("name", "a"),
     new XElement("item",
      new XAttribute("name", "11")),
     new XElement("item",
      new XAttribute("name", "22"))),
    new XElement("header",
     new XAttribute("name", "b"),
     new XElement("item",
      new XAttribute("name", "33")),
     new XElement("item",
      new XAttribute("name", "44")),
     new XElement("item",
      new XAttribute("name", "55")))));

Answer 3

With LINQ:-

var qry = from row in Table
          group row by row.ColumnA into header
          select header;

var elem = new XElement("root");

foreach (var header in qry)
{
  var elemHead = new XElement("header", new XAttribute("name", header.Key));
  elem.Add(elemHead);
  foreach (var item in header)
    elemHead.Add(new XElement("item", new XAttribute("name", item.ColumnB)));
}
// the variable elem contains the result.

Answer 4

OK, second approach after learning that the data is available in a DataTable to begin with.

The code is a bit more involved, since based on a DataTable, you can't really do much in terms of grouping etc. I am building up the XmlDocument (since you're on .NET 2.0) while scanning through the rows of data. I need to keep track of the <header> elements in a dictionary, in order to add a second, third entry with the same "ColumnA" value to that already existing XmlElement in the document - it's a bit involved, but if you study it carefully, I hope you see it's really no trickery or anything - just a bit of bookkeeping along the way of building the XmlDocument:

// create the XmlDocument and add <root> node
XmlDocument doc = new XmlDocument();
doc.AppendChild(doc.CreateElement("root"));

// dictionary to keep track of <header> nodes
Dictionary<string, XmlNode> nodesPerColumnA = new Dictionary<string, XmlNode>();

// Loop through data rows
foreach (DataRow row in tbl.Rows)
{
   // extract values for ColumnA and ColumnB as strings
   string columnAValue = row["ColumnA"].ToString();
   string columnBValue = row["ColumnB"].ToString();

   // create a new <item> XmlNode and fill its attribute @Name 
   XmlElement newNode = doc.CreateElement("item");

   XmlAttribute newNodeAttribute = doc.CreateAttribute("name");
   newNodeAttribute.InnerText = columnBValue;

   newNode.Attributes.Append(newNodeAttribute);

   // check if we already have a <header> node for that "ColumnA" value
   if(nodesPerColumnA.ContainsKey(columnAValue))
   {
       // if so - just add <item> below that <header>
       XmlNode parent = nodesPerColumnA[columnAValue];

       parent.AppendChild(newNode);
   }
   else
   {
       // if not - create appropriate <header> node and its @name attribute
       XmlElement header = doc.CreateElement("header");

       XmlAttribute headerAttr = doc.CreateAttribute("name");
       headerAttr.InnerText = columnAValue;

       header.Attributes.Append(headerAttr);

       header.AppendChild(newNode);

       doc.DocumentElement.AppendChild(header);

       // store that <header> xmlnode into the dictionary for future use
       nodesPerColumnA.Add(columnAValue, header);
    }
 }

 // check the contents of the XmlDocument at the end
 string xmlContents = doc.InnerXml;