Generate XML from DataTable, along with count

[mohan_m01]

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.

[Mike Askew]

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>

[mohan_m01]

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>

[Mike Askew]

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.

[mohan_m01]

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!

[Mike Askew]

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