8

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++;

      }
discorax
  • 1,487
  • 4
  • 24
  • 39
  • Can you add another attribute, wich is a sortable version of the time? Ticks or YYYYMMDD – Ryan Cook Dec 05 '08 at 18:37
  • I'm consuming a web service, and I have a call in to the Adminstrator to have them add the sorting into the stored procedure, but if I can't get a hold of them I have to try to find a solution with the existing XML format. Good suggestion though. – discorax Dec 05 '08 at 18:43
  • All of these answers work in different ways. Thanks guys. All of these are useful. – discorax Dec 05 '08 at 21:43
  • Why, short of simple ignorance, would anyone write a web service which emits XML that doesn't have a schema to conform to? It's like writing an API and then documenting it with a Post-It note. – Robert Rossney Dec 06 '08 at 19:43

6 Answers6

5

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 );
                }
            }
        }
jlew
  • 10,491
  • 1
  • 35
  • 58
  • I tried that and I get an error Cannot convert type System.DateTime to System.Collections.IComparer – discorax Dec 05 '08 at 18:41
  • The other tricky part is that I need to sort the NODES not just the attributes. – discorax Dec 05 '08 at 18:48
  • 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. – jlew Dec 05 '08 at 19:02
5

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.

Timothy Khouri
  • 31,315
  • 21
  • 88
  • 128
  • Hi @Timothy. Your code seems to do what I need. Could you help me with a post I made earlier over here: http://stackoverflow.com/questions/12943635/sort-xml-nodes-alphabetically-on-attribute-name Also, I've never used Linq, so I'm not sure if your code will work on my VB.NET environment. Thanks! – Adam Oct 18 '12 at 14:27
2

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>
Dimitre Novatchev
  • 240,661
  • 26
  • 293
  • 431
1

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").

Robert Rossney
  • 94,622
  • 24
  • 146
  • 218
0

suppose ur date time is in this format

2010-06-01T15:16:29+05:00

then simplest way that can be done is

< xsl:sort select="translate(XPATH_RETURNING_DATE,'-T:+','')" order="descending" data-type="number" />

IN DATETIME JUST REPLACE EXTRA CHARACTERS in my datetime format i have the extra characters ( - T : and + ) so just replace it AND THEN YOUR DATE TIME WILL BE IN NUMBER FORMAT THAT CAN BE SORTED EASILY

Muneer
  • 1
0

I know this Question is quite old and you probably have a solution with you but i like to share my answer:

      private static void  SortElementAttributesBasis(XmlNode rootNode)
    {



        for (int j = 0; j < rootNode.ChildNodes.Count; j++)
        {
            for (int i = 1; i < rootNode.ChildNodes.Count; i++)
            {
                Console.WriteLine(rootNode.OuterXml);
                DateTime dt1 = DateTime.ParseExact(rootNode.ChildNodes[i].Attributes["sTime"].Value, "M/d/yyyy h:mm:ss tt", System.Globalization.CultureInfo.InvariantCulture);
                DateTime dt2 = DateTime.ParseExact(rootNode.ChildNodes[i-1].Attributes["sTime"].Value, "M/d/yyyy h:mm:ss tt", System.Globalization.CultureInfo.InvariantCulture);
                int compare = DateTime.Compare(dt1,dt2);
                if (compare < 0)
                {
                    rootNode.InsertBefore(rootNode.ChildNodes[i], rootNode.ChildNodes[i - 1]);
                    Console.WriteLine(rootNode.OuterXml);
                }

                // Provide the name of Attribute in .Attribute["Name"] based on value you want to sort.

                   //if (String.Compare(rootNode.ChildNodes[i].Attributes["sTime"].Value, rootNode.ChildNodes[1 - 1].Attributes["sTime"].Value) < 0)
                //{
                //    rootNode.InsertBefore(rootNode.ChildNodes[i], rootNode.ChildNodes[i - 1]);

                //}
            }
        }
    }

Input XML is sample provided by @Dimitre Novatchev

<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" />

Output

<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" />

Chetan Mehra
  • 189
  • 1
  • 3
  • 20