Generate XML from DataTable, along with count

Hi,

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.

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

Ie.

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

Or.

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

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>
</M1>

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))
            {
                writer.WriteStartDocument();
                writer.WriteStartElement("Data");

                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.WriteStartElement(ManagerID);
                    writer.WriteAttributeString("Total", Total);

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

        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.

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!

No worries, mark thread as solved if nothing else needs fixing :)