Generate XML from DataTable, along with count


I have an sql result set assigned to a datatable. The result set is of the form

    ManagerId    | VendorId |   Count
M1                V1                  10
M1                V2                  5
M2                V1                  10
M2                V2                  5
M3                V1                  10
M3                V2                  5

I need to convert this into an XML and also, the sum of count needs to be shown at each level. so the output in this case wud be like..

-- M1 - 15

  -- V1    -  10

  -- V2    -  5  

-- M2 - 15

  -- V1    -  10

  -- V2    -  5  

-- M3 - 15

  -- V1    -  10

  -- V2    -  5  

what is the simplest way to achieve this? Pls help.

4 Years
Discussion Span
Last Post by Mike Askew

You haven't said how you want the XML laid out, nobody can help without that information.


<M1 Total='15'>
    <V1 Value='10' />
    <V2 Value='5' />



Thanks a ton for the response!

I need the XML laid out in the below format..

<M1 Total='15'>
    <V1 Value='10' ></V1>
    <V2 Value='5' ></V2>

I have achieved the desired using the following (Apologies for time delay i've never had to do C# to XML before :D, Presuming your using C# ofc forgot to ask that >.<)

static void Main()
            DataTable table = GetTable();

            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;

            using (XmlWriter writer = XmlWriter.Create(@"FilePathHere", settings))

                string currentManager = "";
                int currentTotal = 0;

                List<String> lstManagerCounts = new List<String>();
                foreach (DataRow row in table.Rows) //Get unique managerID's and their totals into a list
                    if (currentManager != row.ItemArray[0].ToString())
                        if (currentManager != "")
                            lstManagerCounts.Add(string.Format("{0},{1}", currentManager, currentTotal));

                        currentManager = row.ItemArray[0].ToString();
                        currentTotal = 0;
                    currentTotal += (int)row.ItemArray[2];
                lstManagerCounts.Add(string.Format("{0},{1}", currentManager, currentTotal));

                foreach (string entry in lstManagerCounts) //Loop the unique ManagerId's finding related entries and listing them
                    string ManagerID = entry.Substring(0, entry.IndexOf(','));
                    string Total = entry.Substring(entry.IndexOf(',')+1);

                    writer.WriteAttributeString("Total", Total);

                    foreach (DataRow dRow in table.Rows)
                        if (dRow.ItemArray[0].ToString() == ManagerID)
                            writer.WriteAttributeString("Value", dRow.ItemArray[2].ToString());

        static DataTable GetTable() //Setting up the datatable using provided example data
            DataTable table = new DataTable();
            table.Columns.Add("ManagerID", typeof(string));
            table.Columns.Add("VendorID", typeof(string));
            table.Columns.Add("Count", typeof(int));
            table.Rows.Add("M1", "V1", 10);
            table.Rows.Add("M1", "V2", 5);
            table.Rows.Add("M2", "V1", 10);
            table.Rows.Add("M2", "V2", 5);
            table.Rows.Add("M3", "V1", 10);
            table.Rows.Add("M3", "V2", 5);
            return table;

It works by looking through the data in a prelim, identifying all unique manager ID's and getting the totals of their entries (as XMLWriter is forwards only).

I then re-loop through the table matching the ManagerID's and creating nodes if they match, else moving onto the next ManagerID.

Hope it helps.

Edited by Mike Askew: Commenting


hi Mikey,

Tried the above code and it worked almost flawless :) ! Thanks a ton for ur inputs. .

Just that the tags M1, M2.. in realtime get replaced by actual names. So node names cannot have a space. As a workaround, i have created a new tag called name and stored the names in those attributes. Thanks again for the help!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.