Obtain child node based on attribute of parent node

Question

I have a CrystalReport report in XML (sorry for the verboseness, I cut out most sample data)

<?xml version="1.0" encoding="UTF-8" ?>
<FormattedReport xmlns = 'urn:crystal-reports:schemas' xmlns:xsi = 'http://www.w3.org/2000/10/XMLSchema-instance'>
<FormattedAreaPair Level="0" Type="Report">
<FormattedAreaPair Level="1" Type="Details">
<FormattedArea Type="Details">
<FormattedSections>
<FormattedSection SectionNumber="0">
<FormattedReportObjects>
<FormattedReportObject xsi:type="CTFormattedField" Type="xsd:string" FieldName="{AIRCRAFT.Tail Number}"><ObjectName>Field2</ObjectName>
<FormattedValue>C-FBCS</FormattedValue>
<Value>C-FBCS</Value>
</FormattedReportObject>
<FormattedReportObject xsi:type="CTFormattedField" Type="xsd:string" FieldName="{AIRCRAFT.Type ID}"><ObjectName>Field8</ObjectName>
<FormattedValue>DHC8</FormattedValue>
<Value>DHC8</Value>
</FormattedReportObject>
<FormattedReportObject xsi:type="CTFormattedField" Type="xsd:unsignedLong" FieldName="{TRIP LEGS.Trip Number}"><ObjectName>Field9</ObjectName>
<FormattedValue>68344</FormattedValue>
<Value>68344.00</Value>
</FormattedReportObject>
</FormattedReportObjects>
</FormattedSection>
</FormattedSections>
</FormattedArea>
</FormattedAreaPair>
<FormattedAreaPair Level="1" Type="Details">
<FormattedArea Type="Details">
<FormattedSections>
<FormattedSection SectionNumber="0">
<FormattedReportObjects>
<FormattedReportObject xsi:type="CTFormattedField" Type="xsd:string" FieldName="{AIRCRAFT.Tail Number}"><ObjectName>Field2</ObjectName>
<FormattedValue>C-FBCS</FormattedValue>
<Value>C-FBCS</Value>
</FormattedReportObject>
<FormattedReportObject xsi:type="CTFormattedField" Type="xsd:string" FieldName="{AIRCRAFT.Type ID}"><ObjectName>Field8</ObjectName>
<FormattedValue>DHC8</FormattedValue>
<Value>DHC8</Value>
</FormattedReportObject>
<FormattedReportObject xsi:type="CTFormattedField" Type="xsd:unsignedLong" FieldName="{TRIP LEGS.Trip Number}"><ObjectName>Field9</ObjectName>
<FormattedValue>68344</FormattedValue>
<Value>68344.00</Value>
</FormattedReportObject>
</FormattedReportObjects>
</FormattedSection>
</FormattedSections>
</FormattedArea>
</FormattedAreaPair>
...
</FormattedAreaPair>
</FormattedReport>

I am attempting to use a LINQ to XML query to extract the Value node based on the parent node's FieldName attribute and place those into an object. There is no unique attribute for Value or the parents of FormattedReportObject nodes. So far here is my code to do so

from fs in xDoc.Descendants("FormattedSection")
select new FlightSchedule
{
  AircraftType = from fos in fs.Descendants("FormattedReportObjects")
                 from fo in fs.Descendants("FormattedReportObject")
                 where fo.Attribute("FieldName").Value.Equals("{AIRCRAFT.Type ID}")
                 from e in fo.Element("Value")
                 select e.Value),
  ....
};

I keep getting errors:

An expression of type 'System.Xml.Linq.XElement' is not allowed in a subsequent from clause in a query expression with source type 'System.Collections.Generic.IEnumerable'. Type inference failed in the call to 'SelectMany')

or if I don't get an error I end up retrieving nothing. Any suggestions would be greatly appreciated on improving my query.

Answer 1

Your code has several problems. First, the thing the compiler is complaining about is, as @MizardX mentioned, that you are using fo.Element("Value") as if it was a sequence. What you probably want is to write let e = fo.Element("Value") (or skip this part completely and directly write select fo.Element("Value").Value).

Another problem is that your XML is using a namespace, but you aren't. This means that you should create a XNamespace object and use it wherever you have element names.

Also, the way your code is written, AircraftType is a sequence of strings. I assume this is not what you wanted.

And seeing that you want to do the same thing for different values of FieldName, you probably want to make this into a method.

With all the problems mentioned above fixed, the code should look something like this:

static readonly XNamespace ns = XNamespace.Get("urn:crystal-reports:schemas");

string GetFieldValue(XElement fs, string fieldName)
{
    return (from fo in fs.Descendants(ns + "FormattedReportObject")
            where fo.Attribute("FieldName").Value == fieldName
            let e = fo.Element(ns + "Value")
            select e.Value).Single();
}
…
var flts = (from fs in xDoc.Descendants(ns + "FormattedSection")
            select new FlightSchedule
            {
                AircraftType = GetFieldValue(fs, "{AIRCRAFT.Type ID}"),
                …
            }).ToList();