How to select a single XML node in c# using multiple XPath queries

Question

I'm trying to select a single node from an XML file based on two queries, I have a product ID for which I need the latest entry - highest issue number.

This is the format of my XML file:

<MyProducts>  
  <Product code="1011234">
    <ProductName>Product Name A</ProductName>
    <ProductId>101</ProductId>
    <IssueNumber>1234</IssueNumber>
  </Product>
  <Product code="1029999">
    <ProductName>Product Name B</ProductName>
    <ProductId>102</ProductId>
    <IssueNumber>9999</IssueNumber>
  </Product>
  <Product code="1015678">
    <ProductName>Product Name A2</ProductName>
    <ProductId>101</ProductId>
    <IssueNumber>5678</IssueNumber>
  </Product>
</MyProducts>

I need to get the <product> node from a ProductId that has the highest IssueNumber. For example if the ProductId is 101 I want the third node, if it's 102, I want the second node. There are around 50 different products in the file, split over three different product ids.

I've tried a number of XPath combinations using SelectSingleNode either by using the specific ProductID and IssueNumber nodes, or by using the code attribute of the product node (which is a combination of Id and Issue) without any success. The code currently uses the code attribute, but only because we're also passing in the issue number and I want to be able to do this without the issue number (to decrease front end maintenance) as it's always the highest issue we want. Current code is this:

XmlNode productNode = productXml.SelectSingleNode("/MyProducts/Product[@code='" + productCode + "']");

I've used these as well, they kind of work, but select the inner nodes, not the outer Product node:

XmlNodeList productNodes = productXml.SelectNodes("/MyProducts/Product/ProductId[text()='101']");
XmlNodeList productNodes = productXml.SelectNodes("/MyProducts/Product[not (../Product/IssueNumber > IssueNumber)]/IssueNumber");

I would like to use a combination of the two, something like this:

XmlNode productNode = productXml.SelectSingleNode("/MyProducts/Product/ProductId[text()='101'] and /MyProducts/Product[not (../Product/IssueNumber > IssueNumber)]/IssueNumber");

But that returns the error "...threw an exception of type 'System.Xml.XPath.XPathException'", but I also expect it won't return the Product node anyway. Can this even be done in a single line, or will I have to loop through the nodes to find the right one?

Answer 1

Use Xml Linq

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

namespace ConsoleApplication167
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            XDocument doc = XDocument.Load(FILENAME);

            var products = doc.Descendants("Product")
                .OrderByDescending(x => (int)x.Element("IssueNumber"))
                .GroupBy(x => (int)x.Element("ProductId"))
                .Select(x => x.First())
                .ToList();

            Dictionary<int, XElement> dict = products
                .GroupBy(x => (int)x.Element("ProductId"), y => y)
                .ToDictionary(x => x.Key, y => y.FirstOrDefault());

            XElement highestId = dict[101];

        }
    }


 
}