Sorting XML nodes based on DateTime attribute C#, XPath

Question

I have a XML Structure that looks like this.

<sales>
  <item name="Games" sku="MIC28306200" iCat="28" 
     sTime="11/26/2008 8:41:12 AM" 
     price="1.00" desc="Item Name" />
  <item name="Games" sku="MIC28307100" iCat="28" 
     sTime="11/26/2008 8:42:12 AM" 
     price="1.00" desc="Item Name" />
...
</sales>

I am trying to find a way to SORT the nodes based on the sTime attribute which is a DateTime.ToString() value. The trick is I need to keep the Nodes in tact and for some reason I can't find a way to do that. I'm fairly certain that LINQ and XPath have a way to do it, but I'm stuck because I can't seem to sort based on DateTime.ToString() value.

XPathDocument saleResults = new XPathDocument(@"temp/salesData.xml");
XPathNavigator navigator = saleResults.CreateNavigator();

XPathExpression selectExpression = navigator.Compile("sales/item/@sTime");
selectExpression.AddSort("@sTime", 
    XmlSortOrder.Descending, 
    XmlCaseOrder.None, 
    "", 
    XmlDataType.Number);

XPathNodeIterator nodeIterator = navigator.Select(selectExpression);

while( nodeIterator.MoveNext() )
    {
         string checkMe = nodeIterator.Current.Value;
    } 

I also need to maintain a pointer to the NODE to retrieve the values of the other attributes.

Perhaps this isn't a simple as I thought it would be.

Thanks.

Solution: Here's what I ended up using. Taking the selected answer and the IComparable class this is how I get the XML nodes sorted based on the sTime attribute and then get the all the attributes into the appropriate Arrays to be used later.

    XPathDocument saleResults = new XPathDocument(@"temp/salesData.xml");
    XPathNavigator navigator = saleResults.CreateNavigator();
    XPathExpression selectExpression = navigator.Compile("sales/item");
    XPathExpression sortExpr = navigator.Compile("@sTime");
    selectExpression.AddSort(sortExpr, new DateTimeComparer());
    XPathNodeIterator nodeIterator = navigator.Select(selectExpression);
    int i = 0;
    while (nodeIterator.MoveNext())
       {
          if (nodeIterator.Current.MoveToFirstAttribute())
          {
              _iNameList.SetValue(nodeIterator.Current.Value, i);
          }
          if (nodeIterator.Current.MoveToNextAttribute())
          {
              _iSkuList.SetValue(nodeIterator.Current.Value, i);
          }
          ...
          nodeIterator.Current.MoveToParent();
          i++;

      }

Solution 1

There's an overload of XPathExpression.Addsort which takes an IComparer interface. If you implement the comparison yourself as IComparer, you could use this mechanism.

 class Program
        {
            static void Main(string[] args)
            {
                XPathDocument saleResults = new XPathDocument( @"salesData.xml" );
                XPathNavigator navigator = saleResults.CreateNavigator( );
                XPathExpression selectExpression = navigator.Compile( "sales/item" );
                XPathExpression sortExpr = navigator.Compile("@sTime");
                selectExpression.AddSort(sortExpr, new DateTimeComparer());
                XPathNodeIterator nodeIterator = navigator.Select( selectExpression );            
                while ( nodeIterator.MoveNext( ) )
                {
                    string checkMe = nodeIterator.Current.Value;
                }
            }
            public class DateTimeComparer : IComparer
            {
                public int Compare(object x, object y)
                {
                    DateTime dt1 = DateTime.Parse( x.ToString( ) );
                    DateTime dt2 = DateTime.Parse( y.ToString( ) );
                    return dt1.CompareTo( dt2 );
                }
            }
        }

Solution 2

Here you go:

XmlDocument myDoc = new XmlDocument();

myDoc.LoadXml(@"
<sales>
<item name=""Games""
    sku=""MIC28306200""
    iCat=""28""
    sTime=""11/26/2008 8:41:12 AM""
    price=""1.00""
    desc=""Item Name"" />
<item name=""Games""
    sku=""MIC28307100""
    iCat=""28""
    sTime=""11/26/2008 8:42:12 AM""
    price=""1.00""
    desc=""Item Name"" />
</sales>
");

var sortedItems = myDoc.GetElementsByTagName("item").OfType<XmlElement>()
    .OrderBy(item => DateTime.ParseExact(item.GetAttribute("sTime"), "MM/dd/yyyy h:mm:ss tt", null));

foreach (var item in sortedItems)
{
    Console.WriteLine(item.OuterXml);
}

That's a Console app that works perfectly.

Solution 3

Here is an XSLT solution:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>

    <xsl:template match="sales">
      <sales>
        <xsl:for-each select="item">
          <xsl:sort select="substring(@sTime,7,4)" data-type="number"/>
          <xsl:sort select="substring(@sTime,1,2)" data-type="number"/>
          <xsl:sort select="substring(@sTime,4,2)" data-type="number"/>
          <xsl:sort select="substring-after(substring-after(@sTime,' '),' ')" />
          <xsl:sort data-type="number" select=
           "translate(
               substring-before(substring-after(@sTime,' '),' '),
               ':', ''
                      )
               " />
          <xsl:copy-of select="."/>
        </xsl:for-each>
      </sales>
    </xsl:template>
</xsl:stylesheet>

When this transformation is applied on the following XML document:

<sales>
    <item name="Games" sku="MIC28306200" iCat="28"
          sTime="11/26/2008 8:41:12 PM"
          price="1.00" desc="Item Name" />
    <item name="Games" sku="MIC28307100" iCat="28"
          sTime="11/26/2008 8:42:12 AM"
                price="1.00" desc="Item Name" />
    <item name="Games" sku="MIC28307100" iCat="28"
          sTime="11/26/2008 11:42:12 AM"
                price="1.00" desc="Item Name" />
    <item name="Games" sku="MIC28306200" iCat="28"
          sTime="12/23/2008 8:41:12 PM"
          price="1.00" desc="Item Name" />
    <item name="Games" sku="MIC28307100" iCat="28"
          sTime="12/23/2008 8:42:12 AM"
                price="1.00" desc="Item Name" />
</sales>

the correct result is produced:

<sales>
   <item name="Games" sku="MIC28307100" iCat="28" sTime="11/26/2008 8:42:12 AM" price="1.00" desc="Item Name"/>
   <item name="Games" sku="MIC28307100" iCat="28" sTime="11/26/2008 11:42:12 AM" price="1.00" desc="Item Name"/>
   <item name="Games" sku="MIC28306200" iCat="28" sTime="11/26/2008 8:41:12 PM" price="1.00" desc="Item Name"/>
   <item name="Games" sku="MIC28307100" iCat="28" sTime="12/23/2008 8:42:12 AM" price="1.00" desc="Item Name"/>
   <item name="Games" sku="MIC28306200" iCat="28" sTime="12/23/2008 8:41:12 PM" price="1.00" desc="Item Name"/>
</sales>

Solution 4

What you're trying to do is accomplished a lot more easily if the XML is properly constructed. The XML Schema recommendation says that date/time values should be represented in ISO8601 format, i.e. CCCC-MM-DD HH:MM:SS. (Actually XML Schema wants the separator between date and time to be a T, and at the moment I don't remember why.)

The two principal advantages of formatting dates and times this way are:

• That's what other users of XML expect, and
• You can sort on their string values.

It's a cruelty to format dates any other way in XML that's going to be processed by XSLT.

It's easy enough to make .NET emit DateTime values in this format (use the "s" format specifier, which stands for - wait for it - "sortable").

View more solutions

Comments

[discorax]

I tried that and I get an error Cannot convert type System.DateTime to System.Collections.IComparer

[discorax]

The other tricky part is that I need to sort the NODES not just the attributes.

[jlew]

See code. You need to select the nodes you want to sort (the item node, and not the sTime attribute), and use an expression representing the sort key expression (the sTime attribute) along with a custom comparer.

[Adam]

Hi @Timothy. Your code seems to do what I need. Could you help me with a post I made earlier over here: stackoverflow.com/questions/12943635/… Also, I've never used Linq, so I'm not sure if your code will work on my VB.NET environment. Thanks!