Juan Pike Juan Pike -4 years ago 93
C# Question

sorting data in XML file into a dataset

so i have the following xml file that my windows form gets from a website.

<WebserviceResult xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="">
<data xmlns="">
<ArrayOfMatches>
<Match>
<TimeSlot>17:00:00</TimeSlot>
<Game_Id>18</Game_Id>
<ArrayOfTeams>
<Team>
<Team_id>14</Team_id>
<Team_Name>New Zealand</Team_Name>
<TeamPlayers>
<Player>
<PlayerId>10</PlayerId>
<PlayerName>Trent</PlayerName>
<PlayerSurname>Boult</PlayerSurname>
</Player>
<Player>...</Player>
<Player>...</Player>
<Player>...</Player>
</TeamPlayers>
</Team>
<Team>
<Team_id>16</Team_id>
<Team_Name>west indies</Team_Name>
<TeamPlayers>...</TeamPlayers>
</Team>
</ArrayOfTeams>
</Match>
</ArrayOfMatches>
</data>




this is just a snippet of the whole thing, the idea here is that two teams are to face eachother, which is assigned a
Game_Id


my idea at the moment is to load the
Game_Id
Team_Id
's and
Team_Name
's into a dataset all in one row, for each
Game_Id
(there are more than one games in the xml file). The team names will eventually end up in a combo box as Team1 V Team2; then when one is selected the
Player
elements will be loaded into 2 separate list boxes.

what i am able to do at the moment is fill different datasets with
TimeSlot
&
Game_Id
, ALL the
TeamPlayers
in the xml file using
DS.ReadXML(xmlRead)
, but not the way i would like, and putting the
Game_Id
's in a combo box with:

XmlElement root = xdoc.DocumentElement;

XmlNodeList nodeList = xdoc.GetElementsByTagName("Match");
foreach (XmlNode xn in nodeList)
{
cb_matches.Items.Add(xn.InnerText);
}


those all work but its not what i am trying to do.

any assistance in this matter would be greatly appreciated, i do feel i have put as much info in here as i can.

EDITED

The
<Date> </Date>
only appears once per web service, there are multiple
<Game_Id>
's on the day. It is used to make sure that the correct XML file is being used.

EDITED

<data xmlns="">
<ArrayOfMatches>
<Match>...</Match>
<Match>...</Match>
</ArrayOfMatches>
</data>


EDITED

so im still struggling more than i should with this.

+-----+-------+---------+------------+---------+-----------+
|Time |Game_Id|Team_A_Id|Team_A_Name |Team_B_Id|Team_B_Name|
|17:00|18 |14 |South Africa|16 |West Indies|
|19:00|19 |18 |New Zealand |12 |England |


that's the structure i want in my
DataTable


now i have been playing with xml-linq etc but i cant seem to lay it out like that.

XDocument xdoc = XDocument.Load(@"c:/matchdata.xml");
IEnumerable<XElement> games = xdoc.Descendants("ArrayOfMatches");

var matches = games
.Select(x => new Games()
{
GameID = x.Element("Game_Id").Value,
TeamAID = x.Element("Team_Id").Value,
TeamAName = x.Element("Team_Name").Value,
TeamBID = x.Element("Team_Id").Value,
TeamBName = x.Element("Team_Name").Value
});

dataGridView1.DataSource = xdoc;


this compiles but doesnt show any data in the
gridview
and it also doesnt show any errors.

Answer Source
var dt = new DataTable();
        dt.Columns.Add(new DataColumn("TimeSlot", typeof(string)));
        dt.Columns.Add(new DataColumn("GameID", typeof(string)));
        dt.Columns.Add(new DataColumn("TeamAID", typeof(string)));
        dt.Columns.Add(new DataColumn("TeamAName", typeof(string)));
        dt.Columns.Add(new DataColumn("TeamBID", typeof(string)));
        dt.Columns.Add(new DataColumn("TeamBName", typeof(string)));

        XDocument xdoc = XDocument.Load(@"c:/matchdata.xml");
        var games = from i in xdoc.Descendants("Match")
            select new //creates a few new anonymous types to store values to dump into the datatable
        {
            TimeSlot = (string)i.Element("TimeSlot"),
            GameID = (string)i.Element("Game_Id"),
            TeamAID = (string)i.Element("ArrayOfTeams").Element("Team").Element("Team_id"),
            TeamAName = (string)i.Element("ArrayOfTeams").Element("Team").Element("Team_Name"),
            TeamBID = (string)i.Element("ArrayOfTeams").Elements("Team").Skip(1).First().Element("Team_id"), //skips a node to collect info for the next team.
            TeamBName = (string)i.Element("ArrayOfTeams").Elements("Team").Skip(1).First().Element("Team_Name") //skips a node to collect info for the next team.
        };          
        foreach (var item in games)
        {
            dt.Rows.Add(item.TimeSlot, item.GameID, item.TeamAID, item.TeamAName, item.TeamBID, item.TeamBName); //adds a new row of data into the datatable for every <Match> </Match>
        }

So this works, dtcan be made the source of a gridviewand you can see the results. This MAY not be the best approach but at least i have learned something, comments on a better/more efficient will be looked at.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download