How do I extract info deep inside XML using C# and LINQ?

Question

This is my first post on StackOverflow, so please bear with me. And I apologize upfront if my code example is a bit long.

Using C# and LINQ, I'm trying to identify a series of third level id elements (000049 in this case) in a much larger XML file. Each third level id is unique, and the ones I want are based on a series of descendant info for each. More specifically, if type == A and location type(old) == vault and location type(new) == out, then I want to select that id. Below is the XML and C# code that I'm using.

In general my code works. As written below it will return an id of 000049 twice, which is correct. However, I have found a glitch. If I remove the first history block that contains type == A, my code still returns an id of 000049 twice when it should only return it once. I know why it is happening, but I can't figure out a better way to run the query. Is there a better way to run my query to get the output I want and still use LINQ?

My XML:

<?xml version="1.0" encoding="ISO8859-1" ?>
<data type="historylist">
    <date type="runtime">
        <year>2011</year>
        <month>04</month>
        <day>22</day>
        <dayname>Friday</dayname>
        <hour>15</hour>
        <minutes>24</minutes>
        <seconds>46</seconds>
    </date>
    <customer>
        <id>0001</id>
        <description>customer</description>
        <mediatype>
            <id>kit</id>
            <description>customer kit</description>
            <volume>
                <id>000049</id>
                <history>
                    <date type="optime">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                        <hour>03</hour>
                        <minutes>00</minutes>
                        <seconds>02</seconds>
                    </date>
                    <userid>batch</userid>
                    <type>OD</type>
                    <location type="old">
                        <repository>vault</repository>
                        <slot>0</slot>
                    </location>
                    <location type="new">
                        <repository>out</repository>
                        <slot>0</slot>
                    </location>
                    <container>0001.kit.000049</container>
                    <date type="movedate">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                    </date>
                </history>
                <history>
                    <date type="optime">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                        <hour>06</hour>
                        <minutes>43</minutes>
                        <seconds>33</seconds>
                    </date>
                    <userid>vaultred</userid>
                    <type>A</type>
                    <location type="old">
                        <repository>vault</repository>
                        <slot>0</slot>
                    </location>
                    <location type="new">
                        <repository>out</repository>
                        <slot>0</slot>
                    </location>
                    <container>0001.kit.000049</container>
                    <date type="movedate">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                    </date>
                </history>
                <history>
                    <date type="optime">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                        <hour>06</hour>
                        <minutes>43</minutes>
                        <seconds>33</seconds>
                    </date>
                    <userid>vaultred</userid>
                    <type>S</type>
                    <location type="old">
                        <repository>vault</repository>
                        <slot>0</slot>
                    </location>
                    <location type="new">
                        <repository>out</repository>
                        <slot>0</slot>
                    </location>
                    <container>0001.kit.000049</container>
                    <date type="movedate">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                    </date>
                </history>
                <history>
                    <date type="optime">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                        <hour>06</hour>
                        <minutes>45</minutes>
                        <seconds>00</seconds>
                    </date>
                    <userid>batch</userid>
                    <type>O</type>
                    <location type="old">
                        <repository>out</repository>
                        <slot>0</slot>
                    </location>
                    <location type="new">
                        <repository>site</repository>
                        <slot>0</slot>
                    </location>
                    <container>0001.kit.000049</container>
                    <date type="movedate">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                    </date>
                </history>
                <history>
                    <date type="optime">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                        <hour>11</hour>
                        <minutes>25</minutes>
                        <seconds>59</seconds>
                    </date>
                    <userid>ihcmdm</userid>
                    <type>A</type>
                    <location type="old">
                        <repository>out</repository>
                        <slot>0</slot>
                    </location>
                    <location type="new">
                        <repository>site</repository>
                        <slot>0</slot>
                    </location>
                    <container>0001.kit.000049</container>
                    <date type="movedate">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                    </date>
                </history>
                <history>
                    <date type="optime">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                        <hour>11</hour>
                        <minutes>25</minutes>
                        <seconds>59</seconds>
                    </date>
                    <userid>ihcmdm</userid>
                    <type>S</type>
                    <location type="old">
                        <repository>out</repository>
                        <slot>0</slot>
                    </location>
                    <location type="new">
                        <repository>site</repository>
                        <slot>0</slot>
                    </location>
                    <container>0001.kit.000049</container>
                    <date type="movedate">
                        <year>2011</year>
                        <month>04</month>
                        <day>22</day>
                        <dayname>Friday</dayname>
                    </date>
                </history>
            </volume>
            ...

My C# code:

IEnumerable<XElement> caseIdLeavingVault =
    from volume in root.Descendants("volume")
    where
        (from type in volume.Descendants("type")
         where type.Value == "A"
         select type).Any() &&
        (from locationOld in volume.Descendants("location")
         where
             ((String)locationOld.Attribute("type") == "old" &&
              (String)locationOld.Element("repository") == "vault") &&
             (from locationNew in volume.Descendants("location")
              where
                  ((String)locationNew.Attribute("type") == "new" &&
                   (String)locationNew.Element("repository") == "out")
              select locationNew).Any()
         select locationOld).Any()
    select volume.Element("id");

    ...

foreach (XElement volume in caseIdLeavingVault)
{
    Console.WriteLine(volume.Value.ToString());
}

Thanks.

---

OK guys, I'm stumped again. Given this same situation and @Elian's solution below (which works great), I need the "optime" and "movedate" dates for the history used to select the id. Does that make sense? I was hoping to end with something like this:

select new { 
    id = volume.Element("id").Value, 

    // this is from "optime"
    opYear = <whaterver>("year").Value, 
    opMonth = <whatever>("month").Value, 
    opDay = <whatever>("day").Value, 

    // this is from "movedate"
    mvYear = <whaterver>("year").Value, 
    mvMonth = <whatever>("month").Value, 
    mvDay = <whatever>("day").Value 
} 

I have tried so many different combinations, but the Attributes for <date type="optime"> and <date type="movedate"> keep getting in my way and I can't seem to get what I want.

---

OK. I found a solution that works well:

select new {
    caseId = volume.Element("id").Value,

    // this is from "optime"
    opYear = volume.Descendants("date").Where(t => t.Attribute("type").Value == "optime").First().Element("year").Value,
    opMonth = volume.Descendants("date").Where(t => t.Attribute("type").Value == "optime").First().Element("month").Value,
    opDay = volume.Descendants("date").Where(t => t.Attribute("type").Value == "optime").First().Element("day").Value,

    // this is from "movedate"
    mvYear = volume.Descendants("date").Where(t => t.Attribute("type").Value == "movedate").First().Element("year").Value,
    mvMonth = volume.Descendants("date").Where(t => t.Attribute("type").Value == "movedate").First().Element("month").Value,
    mvDay = volume.Descendants("date").Where(t => t.Attribute("type").Value == "movedate").First().Element("day").Value
};

However, it does fail when it finds an id with no "movedate". A few of these exist, so now I am working on that.

---

Well, late yesterday afternoon I finally figured out the solution I had been wanting:

var caseIdLeavingSite =
    from volume in root.Descendants("volume")
    where volume.Elements("history").Any(
        h => h.Element("type").Value == "A" &&
        h.Elements("location").Any(l => l.Attribute("type").Value == "old" && ((l.Element("repository").Value == "site") ||
                                                                               (l.Element("repository").Value == "init"))) &&
        h.Elements("location").Any(l => l.Attribute("type").Value == "new" && l.Element("repository").Value == "toVault")
        )
    select new {
        caseId = volume.Element("id").Value,
        opYear = volume.Descendants("date").Where(t => t.Attribute("type").Value == "optime").First().Element("year").Value,
        opMonth = volume.Descendants("date").Where(t => t.Attribute("type").Value == "optime").First().Element("month").Value,
        opDay = volume.Descendants("date").Where(t => t.Attribute("type").Value == "optime").First().Element("day").Value,
        mvYear = (volume.Descendants("date").Where(t => t.Attribute("type").Value == "movedate").Any() == true) ? 
                 (volume.Descendants("date").Where(t => t.Attribute("type").Value == "movedate").First().Element("year").Value) : "0",
        mvMonth = (volume.Descendants("date").Where(t => t.Attribute("type").Value == "movedate").Any() == true) ? 
                  (volume.Descendants("date").Where(t => t.Attribute("type").Value == "movedate").First().Element("month").Value) : "0",
        mvDay = (volume.Descendants("date").Where(t => t.Attribute("type").Value == "movedate").Any() == true) ? 
                (volume.Descendants("date").Where(t => t.Attribute("type").Value == "movedate").First().Element("day").Value) : "0"
   };

This satisfies the requirements that @Elian helped with and grabs the additional date info necessary. It also accounts for those few instances when there is no element for "movedate" by using the ternary operator ?:.

Now, if anyone knows how to make this more efficient, I'm still interested. Thanks.

Answer 1

I think you want something like this:

IEnumerable<XElement> caseIdLeavingVault =
    from volume in document.Descendants("volume")
    where volume.Elements("history").Any(
        h => h.Element("type").Value == "A" &&
            h.Elements("location").Any(l => l.Attribute("type").Value == "old" && l.Element("repository").Value == "vault") &&
            h.Elements("location").Any(l => l.Attribute("type").Value == "new" && l.Element("repository").Value == "out")
        )
    select volume.Element("id");

Your code independently checks if a volume has a <history> element of type A and a (not necessarily the same) <history> element which has the required <location> elements.

The code above checks if there exists a <history> element that is both of type A and contains the required <location> elements.

Update: Abatishchev suggested a solution that uses an xpath query instead of LINQ to XML, but his query is too simple and doesn't return exactly what you asked for. The following xpath query will do the trick, but it's also a little bit longer:

data/customer/mediatype/volume[history[type = 'A' and location[@type = 'old' and repository = 'vault'] and location[@type = 'new' and repository = 'out']]]/id

Answer 2

What for do you use such complex and expensive LINQ to XML query when you can use simple XPath query:

using System.Xml;

string xml = @"...";
string xpath = "data/customer/mediatype/volume/history/type[text()='A']/../location[@type='old' or @type='new']/../../id";

var doc = new XmlDocument();
doc.LoadXml(xml); // or use Load(path);

var nodes = doc.SelectNodes(xpath);

foreach (XmlNode node in nodes)
{
    Console.WriteLine(node.InnerText); // 000049
}

---

or if you don't need XML DOM model:

using System.Xml.XPath;

XPathDocument doc = null;
using (var stream = new StringReader(xml))
{
    doc = new XPathDocument(stream); // specify just path to file if you have such one
}
var nav = doc.CreateNavigator();
XPathNodeIterator nodes = (XPathNodeIterator)nav.Evaluate(xpath);
foreach (XPathNavigator node in nodes)
{
    Console.WriteLine(node.Value);
}