One Step Search Through XPath Query and Linq To Xml

As xml is main format of data exchange between systems, being comfortable of navigation of xml data is very important for xml handling. There are many ways a search can be performed on an xml document, but here I am trying to demo how to perform xml search using xpath query and linq to xml which in my view are two of the easiest and most clean way of handling complex xml data, the real scenario could be mix of both, I deliberately did this in order to show how each mechanism is working without each other and hoping that under some restrictive circumstances there is still one option left to use.

Another challenge for this exercise is I want just to use ONE QUERY to get to where ever the data is and totally avoid the need of any looping through collections as that I believe is exactly why we need a query engine in the first place.

Here is an example of my xml document as a result of web service response.

<Reply>
  <GetCurrencyConversionsReply>
    <AllCurrencyConversions>
      <CurrencyConversionSet>
        <FromCurrency>EUR</FromCurrency>
        <ToCurrency>XPF</ToCurrency>
        <CurrencySubCode>XPF</CurrencySubCode>
        <CurrencyConversions>
          <CurrencyConversion>
            <DateFrom>2008-01-01</DateFrom>
            <DateTo>2009-01-09</DateTo>
            <ConversionRate>119.331742200000</ConversionRate>
            <IsMultiplier>Y</IsMultiplier>
          </CurrencyConversion>
          <CurrencyConversion>
            <DateFrom>2009-01-10</DateFrom>
            <DateTo>2029-12-31</DateTo>
            <ConversionRate>119.331700000000</ConversionRate>
            <IsMultiplier>Y</IsMultiplier>
          </CurrencyConversion>
        </CurrencyConversions>
      </CurrencyConversionSet>
      <CurrencyConversionSet>
        <FromCurrency>USD</FromCurrency>
        <ToCurrency>XPF</ToCurrency>
        <CurrencySubCode>XPF</CurrencySubCode>
        <CurrencyConversions>
          <CurrencyConversion>
            <DateFrom>2009-12-09</DateFrom>
            <DateTo>2010-03-21</DateTo>
            <ConversionRate>90.000000000000</ConversionRate>
            <IsMultiplier>Y</IsMultiplier>
          </CurrencyConversion>
          <CurrencyConversion>
            <DateFrom>2010-03-22</DateFrom>
            <DateTo>2010-08-31</DateTo>
            <ConversionRate>100.000000000000</ConversionRate>
            <IsMultiplier>Y</IsMultiplier>
          </CurrencyConversion>
          <CurrencyConversion>
            <DateFrom>2010-09-01</DateFrom>
            <DateTo>2029-01-22</DateTo>
            <ConversionRate>90.000000000000</ConversionRate>
            <IsMultiplier>Y</IsMultiplier>
          </CurrencyConversion>
          <CurrencyConversion>
            <DateFrom>2029-01-23</DateFrom>
            <DateTo>2029-12-31</DateTo>
            <ConversionRate>80.000000000000</ConversionRate>
            <IsMultiplier>Y</IsMultiplier>
          </CurrencyConversion>
        </CurrencyConversions>
      </CurrencyConversionSet>
      <CurrencyConversionSet>
        <FromCurrency>XPF</FromCurrency>
        <ToCurrency>EUR</ToCurrency>
        <CurrencySubCode>XPF</CurrencySubCode>
        <CurrencyConversions>
          <CurrencyConversion>
            <DateFrom>2008-01-01</DateFrom>
            <DateTo>2009-01-09</DateTo>
            <ConversionRate>119.331742200000</ConversionRate>
            <IsMultiplier>N</IsMultiplier>
          </CurrencyConversion>
          <CurrencyConversion>
            <DateFrom>2009-01-10</DateFrom>
            <DateTo>2029-12-31</DateTo>
            <ConversionRate>119.331700000000</ConversionRate>
            <IsMultiplier>N</IsMultiplier>
          </CurrencyConversion>
        </CurrencyConversions>
      </CurrencyConversionSet>
      <CurrencyConversionSet>
        <FromCurrency>XPF</FromCurrency>
        <ToCurrency>XPF</ToCurrency>
        <CurrencySubCode>XPF</CurrencySubCode>
        <CurrencyConversions>
          <CurrencyConversion>
            <DateFrom>2009-12-16</DateFrom>
            <DateTo>2029-12-31</DateTo>
            <ConversionRate>1.000000000000</ConversionRate>
            <IsMultiplier>Y</IsMultiplier>
          </CurrencyConversion>
        </CurrencyConversions>
      </CurrencyConversionSet>
    </AllCurrencyConversions>
  </GetCurrencyConversionsReply>
</Reply>

The xpath query is by far the most widely used option and easiest to learn, strange enough this two in this case come hand in hand, the example is given in c#, using System.Xml.XPath, but it does not mean it only works for c#, xpath query is widely avaliable in other languages as it is supported as an xml standard, you can find full reference of query language from here.

string querystr = "//CurrencyConversionSet[FromCurrency=\"" 
+ FromCurrency.ToUpper() +
"\" and ToCurrency=\"" + ToCurrency.ToUpper()
 + "\"]/CurrencyConversions/CurrencyConversion[number(
concat(substring(DateFrom,1,4),
substring(DateFrom,6,2),substring(DateFrom,9,2))) <=" +
  todayint.ToString() + "  and number(concat(substring(DateTo,1,4),
substring(DateTo,6,2),substring(DateTo,9,2)))>=" 
+ todayint.ToString() + "]";

var node = (doc.XPathSelectElements(@querystr)).FirstOrDefault();

 if (node != null)
 {
              
  string exchangestr = node.Element("ConversionRate").Value;
  string multiplystr = node.Element("IsMultiplier").Value;

 }
 else
 {
    // logging
  }

A couple of points:

1. If you work in C#, always use XElement in System.Xml.Linq, rather than XMLElement in dom, as the later is very old.

2. Xpath is case sensitive

3. You can add filters [] at any level you like which is fantastic, this ensures that you can do a complex query in just one sentence and to get rid of any further looping of subset.

4. The example is showing the query based on element value, there are heaps of examples that are based on attribute value.

5. String parameter need to be encoded with double quote, otherwise it will be taken as integer.

6. Less than and great than can not be performed on string, so need to convert object to number using number() function

7. A quick reference of xpath tree level operators
// — all descendants
/ —- direct child
[elementname] – direct child elemenet reference at referencing level in a predicate.

There might be some hardcore Linq fans out there and they prefer to write linq statement rather than some kind of machine language as above, the good news is we also can do that, most of linq to xml examples are given in just one level of query, as the above example, the query could be desired at multiple levels.

var nodequery = from _setnodes in doc.Descendants("CurrencyConversionSet")
                         from  _fromCurrencyNodes in _setnodes.Elements("FromCurrency")
                         where _fromCurrencyNodes.Value == FromCurrency.ToUpper()
                           from _toCurrencyNodes in _setnodes.Elements("ToCurrency")
                           where _toCurrencyNodes.Value == ToCurrency.ToUpper()
                          from _conversionnode in      _setnodes.Elements("CurrencyConversions").
Elements("CurrencyConversion")
                        where _conversionnode.Element("DateFrom").Value.
CompareTo(todaystr)<=0
               && _conversionnode.Element("DateTo").
Value.CompareTo(todaystr) >= 0

select _conversionnode;
            
            
var node = nodequery.FirstOrDefault();            

if (node != null)
{
     string exchangestr = node.Element("ConversionRate").Value;
     string multiplystr = node.Element("IsMultiplier").Value;

}
else
{
//logging 
}

A couple of points of this piece of code
1. You can use xpath query in linq to xml through XPathSelectElements, but the point here is using the linq query
2. Linq to xml can have more complicated string comparions through .CompareTo
3. It shows how to perform complex query on child levels within one linq statement.

Tags: ,

This entry was posted on Wednesday, September 29th, 2010 at 2:09 am and is filed under ASP.NET. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

*