Filter data from Xml according to date in C#

Question

I need help filtering xml file according to dates , with this part of the code that i have it only prints out all average information according to user name and place

        Runner run = new Runner();
       string filePath = "runners.xml"; //path
        XDocument xDoc = XDocument.Load(filePath);
        string userSelect = name;



        var averageAddDistancequery = xDoc.Descendants("User").Where(w => (string)w.Element("Name") == user).Select(s => new
        {
            add = s.Elements("Attempts").Where(w => (string)w.Element("Place").Value == "Paris").Select(t => t.Element("Distance").Value)
        }).ToList();

        if (averageAddDistancequery[0].add.Count() > 0) 
        {
            var aa = averageAddDistancequery[0].add.Average(a => float.Parse(a));
            run.averageDistance = aa.ToString();        
        }
        else
        {
           // nothing
        }

        var averageAdd2Distancequery = xDoc.Descendants("User").Where(w => (string)w.Element("Name") == userSelector).Select(s => new
        {
            add = s.Elements("Attempts").Where(w => (string)w.Element("Place").Value == "Madrid").Select(t => t.Element("Distance").Value)
        }).ToList();

        if (averageAdd2Distancequery[0].add.Count() > 0)
        {
            var aa = averageAdd2DistanceSubquery[0].add.Average(a => float.Parse(a));
            run.averageDistance2 = aa.ToString();
        }
        else
        {
           // nothing
        }



        xmlDoc.DocumentElement.SetAttribute("searching", user);
        XmlNodeList tests = xmlDoc.SelectNodes("//User[Name =/*/@searching]/Attempts");
        listBox1.Items.Clear();
        listBox1.Items.Add("Runners Name: " + user);
        listBox1.Items.Add("Overall  Distance in Paris: " + run.averageAdd);
        listBox1.Items.Add("Overall Distance in Madrid: " + run.averageSub);

For example if my xml file looks like this

    Users>
    <User>
     <Name>David</Name>
      <Attempts>
       <Place>Paris</Place>
       <Date>3/29/2012</Date>
       <Distance>100</Distance>
     </Attempts>
     <Attempts>
      <Place>Madrid</Place>
      <Date>7/28/2012</Date>
      <Distance>100</Distance>
     </Attempts>
     <Attempts>
      <Place>Paris</Place>
      <Date>8/19/2012</Date>
      <Distance>60</Distance>
     </Attempts>
     <Attempts>
      <Place>Madrid</Place>
      <Date>9/29/2012</Date>
      <Distance>200</Distance>
    </Attempts>  
   </User>
   <User>
    <Name>Lenny</Name>
     <Attempts>
      <Place>Paris</Place>
      <Date>9/29/2012</Date>
      <Distance>130</Distance>
     </Attempts>
  </User>
 </Users>

If i run the code for david it will print out something like this

User:David

Average Distance in Paris:// data

Average Distance in Madrid: // data

This is not what i want, what i want is to select any two dates lets from a textbox and display only the information between those two dates For example if i chose david, from date 3/29/2012 to 8/29/2012

I would want and output something like this:

User: David

Average Distance in Paris from 3/29/2012 to 8/29/2012: //data

Average Distance in Madrid from 3/29/2012 to 8/29/2012: //data

Ive been trying for hours, i need help implementing this

You want to use XQuery for this. Check it out here w3schools.com/xquery/default.asp — lahsrah
– lahsrah, Commented Oct 2, 2012 at 4:02

cuongle · Accepted Answer · 2012-10-02 05:00:25Z

1

Use LINQ to XML, assume in here two dates you select from TextBox:

var userElement = xDox.Descendants("User")
                .SingleOrDefault(u => u.Element("Name").Value == "David");

if (userElement != null)
{
    var result = userElement.Descendants("Attempts")
        .Select(a => new
            {
                Place = a.Element("Place").Value,
                Date = DateTime.Parse(a.Element("Date").Value),
                Distance = int.Parse(a.Element("Distance").Value)
            })

        .Where(a => a.Date >= DateTime.Parse("3/29/2012")
                    && a.Date <= DateTime.Parse("8/29/2012"))

        .GroupBy(a => a.Place)
        .Select(g => new {Place = g.Key, Avg = g.Average(x => x.Distance)});
}

answered Oct 2, 2012 at 5:00

cuongle

75.5k30 gold badges155 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Israel Rodriguez Over a year ago

how would i display it on a listbox or textbox?

cuongle Over a year ago

@IsraelRodriguez: It returns to a list, loop a list to build string in order to show on the textbox, if you don't know, you should ask another question

horgh · Accepted Answer · 2012-10-02 05:27:09Z

1

You can do, what you need, with Linq to Xml:

XElement x = XElement.Load("In.xml");
IFormatProvider f = new System.Globalization.CultureInfo("en-US");

DateTime bdate = DateTime.Parse("3/29/2012", f);
DateTime edate = DateTime.Parse("8/29/2012", f);
string username = "David";

var info = x.Elements("User")
            .Where(u => u.Element("Name").Value == username)
            .Select(u => new
{
  Name = u.Element("Name").Value,                   //user name
  AverageAttempts = u.Elements("Attempts")          //select user's attempts 
                     .Where(a =>                    //filter by dates
                     {
                        DateTime d = DateTime.Parse(a.Element("Date").Value, f);
                        return d >= bdate && d <= edate;
                     })
                     .GroupBy(a => a.Element("Place").Value) //group by place
                     .Select(g => new         // create summary info by place
                     {
                        Place = g.Key,              //place
                        BeginDate = g.Elements("Date") 
                                     .Select(d => DateTime.Parse(d.Value, f))
                                     .Min(),   //min date, i.e. first attempt
                        EndDate = g.Elements("Date")   
                                   .Select(d => DateTime.Parse(d.Value, f))
                                   .Max(),   //max date, i.e. last attempt
                        Distance = g.Elements("Distance")//average distance
                                    .Average(d => decimal.Parse(d.Value))
                     })
})
.FirstOrDefault();

if(info!=null)
{
   Console.WriteLine(info.Name);
   foreach (var aa in info.AverageAttempts)
   {
       Console.WriteLine(string.Format("{0} [{1} - {2}]:\t{3}",
                                       aa.Place,
                                       aa.BeginDate,
                                       aa.EndDate,
                                       aa.Distance));
   }
}

The output contains not the dates used to filter, but the actual min and max dates of the user attempts.

This code, of course, doesn't contain any validity checks on whether all the necessary xml tags present in the file, or whether values are valid dates and decimals...It's up to you to modify it under your certain needs.

edited Oct 2, 2012 at 5:27

answered Oct 2, 2012 at 4:46

horgh

18.6k22 gold badges70 silver badges125 bronze badges

3 Comments

Israel Rodriguez Over a year ago

thanks, would it work on a windows form app? also i need to provide a name for the user on a textbox, and i can chose the dates i want, for example a start date will be in a textbox and enddate will be in another. Also it should display one user at a time

horgh Over a year ago

@IsraelRodriguez Of course it will. You only need to replace Console with your windows forms editors...

horgh Over a year ago

@IsraelRodriguez I've modified my answer a little, have a look at it

Chuck Savage · Accepted Answer · 2012-10-02 06:33:21Z

0

You can use this Xml library that will enable to read the dates and sort by them.

XElement root = XElement.Load(file);
XElement david = root.XPathElement("//User[Name={0}]", "David");
XElement[] attempts = david
         .XPath("Attempts[Date>={0} and Date<={1}]",
                new DateTime(2012, 3, 29), new DateTime(2012, 8, 29))
         .ToArray();

answered Oct 2, 2012 at 6:33

Chuck Savage

12k6 gold badges53 silver badges69 bronze badges

Collectives™ on Stack Overflow

Filter data from Xml according to date in C#

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related