how to query xml with where clause?

Question

I am using linq to xml to extract a list of Station data from a xmlstring xmlData. The xmldata looks like this:

<Observations>
  <Observation>
    <Station>ADELONG POST OFFICE</Station>
    <DateTime>2010-09-02T00:00:00</DateTime>
    <Temperature>19.212989033764689</Temperature>
  </Observation>
 <Observation>
    <Station>ADELONG POST OFFICE</Station>
    <DateTime>2010-09-01T00:00:00</DateTime>
    <Temperature>28.529448969536205</Temperature>
  </Observation>
  <Observation>
    <Station>ALBURY AIRPORT</Station>
    <DateTime>2010-09-01T00:00:00</DateTime>
    <Temperature>34.687027630716109</Temperature>
  </Observation>
  <Observation>
    <Station>ALBURY AIRPORT AWS</Station>
    <DateTime>2010-09-01T00:00:00</DateTime>
    <Temperature>28.131385570453197</Temperature>
  </Observation>
</Observations>

I am trying to query this xmlstring to extract the name of the station for only the most recent datetime. I also would like to retrieve the most recent temperature.

I have defined a station class:

public class Station
{
    public string Name { get; set; }
    public DateTime MostRecentDate { get; set; }
    public decimal LastTemperature { get; set; }
}

How can I list the most recent temperature+datetime+name for each station ?

I am running this in a c# console application (complete code):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Linq;

namespace weatherxml
{
    class Program
    {
        static void Main(string[] args)
        {
            string xmlData = @"<Observations><Observation><Station>ADELONG POST OFFICE</Station><DateTime>2010-09-02T00:00:00</DateTime><Temperature>19.212989033764689</Temperature></Observation><Observation><Station>ADELONG POST OFFICE</Station><DateTime>2010-09-01T00:00:00</DateTime><Temperature>28.529448969536205</Temperature></Observation><Observation><Station>ALBURY AIRPORT</Station><DateTime>2010-09-01T00:00:00</DateTime><Temperature>34.687027630716109</Temperature></Observation><Observation><Station>ALBURY AIRPORT AWS</Station><DateTime>2010-09-01T00:00:00</DateTime><Temperature>28.131385570453197</Temperature></Observation></Observations>";
            XDocument weatherData = XDocument.Parse(xmlData);

            var stations = from item in weatherData.Descendants("Observation")
                           select new Station
                           {
                               Name = item.Element("Station").Value,
                               MostRecentDate = DateTime.Parse(item.Element("DateTime").Value),
                               LastTemperature = Decimal.Parse(item.Element("Temperature").Value)

                           };

            var st = stations;

            foreach (var s in st)
            {
                Console.WriteLine(s.Name);
            }
            Console.ReadLine();
        }
    }

    public class Station
    {
        public string Name { get; set; }
        public DateTime MostRecentDate { get; set; }
        public decimal LastTemperature { get; set; }

    }
}

score 0 · Answer 1 · edited May 23 '17 at 10:26

Here's a Link to another StackOverflow question similar to yours

Converted to your requirement you should have a code similar to this:

var op = from station in stations
                 group station by station.Name into grp
                 let recentTimestamp = grp.OrderByDescending(t => t.MostRecentDate).FirstOrDefault()
                 select recentTimestamp;

You can as well try to do away with the second LINQ Query given by me and combine these with your own if at all you only want the recent items to be picked.

score 0 · Answer 2 · answered Mar 06 '14 at 03:59

Use Groupby clause

var st = from s in stations group s by s.Name into result select new { Station = result.Key, MostRecent = result.Max(item => item.MostRecentDate) };

            foreach (var s in st)
            {
                Console.WriteLine(s.MostRecent);
                Console.WriteLine(s.Station);
            }

score 0 · Answer 3 · answered Mar 06 '14 at 05:01

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace weatherxml
{
    class Program
    {
        static void Main(string[] args)
        {
            string xmlData = @"<Observations><Observation><Station>ADELONG POST OFFICE</Station><DateTime>2010-09-02T00:00:00</DateTime><Temperature>19.212989033764689</Temperature></Observation><Observation><Station>ADELONG POST OFFICE</Station><DateTime>2010-09-01T00:00:00</DateTime><Temperature>28.529448969536205</Temperature></Observation><Observation><Station>ALBURY AIRPORT</Station><DateTime>2010-09-01T00:00:00</DateTime><Temperature>34.687027630716109</Temperature></Observation><Observation><Station>ALBURY AIRPORT AWS</Station><DateTime>2010-09-01T00:00:00</DateTime><Temperature>28.131385570453197</Temperature></Observation></Observations>";
            var stl = new List<Station>();
            using (DataSet ds = new DataSet())
            {
                ds.ReadXml(new StringReader(xmlData));

                //i want proper types, not strings
                DataColumn dc = ds.Tables[0].Columns.Add("dt", typeof(DateTime));
                dc.Expression = "DateTime";
                dc = ds.Tables[0].Columns.Add("temp", typeof(Decimal));
                dc.Expression = "Temperature";

                //group by
                var result = ds.Tables[0].AsEnumerable().GroupBy(a => a.Field<string>("Station")).Select(g => g.OrderByDescending(a => a.Field<DateTime>("dt")));

                //create result
                foreach(var i in result)
                {
                    var fi = i.First();
                    stl.Add(new Station() { Name = fi.Field<string>("Station"), LastTemperature = fi.Field<decimal>("temp"), MostRecentDate = fi.Field<DateTime>("dt") });
                }
                //here the stationList (stl) has info for all stations
            }
            Console.ReadLine();
        }
    }

    public class Station
    {
        public string Name { get; set; }
        public DateTime MostRecentDate { get; set; }
        public decimal LastTemperature { get; set; }

    }
}

score 0 · Accepted Answer · answered Mar 06 '14 at 05:34

Try to group your data by station name, then select observation having maximum date from each group. This linq query should work :

var stations = from item in weatherData.Descendants("Observation")
               group item by (string) item.Element("Station")
               into g
               let date = g.Max(o => DateTime.Parse((string)o.Element("DateTime")))
                select new 
                {
                    Name = g.Key,
                    MostRecentDate = date,
                    LastTemperature = Decimal.Parse((string)g.FirstOrDefault(o => date == DateTime.Parse((string)o.Element("DateTime"))).Element("Temperature"))
                };

But this way is more readable :

var query = from item in weatherData.Descendants("Observation")
            group item by (string) item.Element("Station")
            into g
            select g;
var stations = new List<Station>();
foreach (var q in query)
{
    //maximum date in current group
    var maxDate = q.Max(o => DateTime.Parse((string) o.Element("DateTime")));
    //1st observation having date equal maximum date
    var latestObservation = q.FirstOrDefault(o => DateTime.Parse((string) o.Element("DateTime")) == maxDate);

    //create new Station using data from latest observation
    var newStation = new Station();
    newStation.Name = q.Key;
    newStation.MostRecentDate = maxDate;
    newStation.LastTemperature = Decimal.Parse((string)latestObservation.Element("Temperature"));

    //add newly created Station to result list
    stations.Add(newStation);
}

how to query xml with where clause?

4 Answers4