4 Replies Latest reply: Sep 6, 2011 7:14 PM by user6736659 RSS

    How to get the Max value of elements of type datetime

    user6736659
      Hi All ,
      I wanted to get the latest timestamp value ( datatype as datetime) from node list. I have following xml. In this xml, i want to get the modifydate element which has latest timestamp through xquery/xpath, it should return me following result 2011-09-29T17:21:17+10:00

      <CustomerList>
      <CustomerDetails>
      <name> Test 1</name>
      <status>active</status>
      <modifyDate>2011-08-20T17:21:17+10:00</modifyDate>
      </CustomerDetails>

      <CustomerDetails>
      <name> Test 2</name>
      <status>cancel</status>
      <modifyDate>2011-08-29T17:21:17+10:00</modifyDate>
      </CustomerDetails>

      <CustomerDetails>
      <name> Test 3</name>
      <status>active</status>
      <modifyDate>2011-09-29T17:21:17+10:00</modifyDate>
      </CustomerDetails>
      </CustomerList>


      i tried to use the
      //CustomerList/CustomerDetails/modifyDate[not(. <=../preceding-sibling::CustomerList/CustomerDetails/modifyDate) and not(. <=../following-sibling::CustomerList/CustomerDetails/modifyDate)]
      But it is not working on datetime datatype.
      Any help in this regard is much appreciated.

      Regards

      Edited by: user6736659 on Sep 5, 2011 6:15 AM
        • 1. Re: How to get the Max value of elements of type datetime
          AlexAnd
          I wanted to get the latest timestamp value ( datatype as datetime) from node list.
          latest timestamp value - is latest in xml
          or
          max value of timestamp value in xml
          ?
          • 2. Re: How to get the Max value of elements of type datetime
            odie_63
            Hi,

            The following XQuery should give you what you want :
            max(
             for $i in /CustomerList/CustomerDetails/modifyDate
             return xs:dateTime($i)
            )
            For instance, using Oracle SQL :
            SQL> var xmldoc varchar2(4000)
            SQL> begin
              2   :xmldoc := '<CustomerList>
              3  <CustomerDetails>
              4  <name> Test 1</name>
              5  <status>active</status>
              6  <modifyDate>2011-08-20T17:21:17+10:00</modifyDate>
              7  </CustomerDetails>
              8  
              9  <CustomerDetails>
             10  <name> Test 2</name>
             11  <status>cancel</status>
             12  <modifyDate>2011-08-29T17:21:17+10:00</modifyDate>
             13  </CustomerDetails>
             14  
             15  <CustomerDetails>
             16  <name> Test 3</name>
             17  <status>active</status>
             18  <modifyDate>2011-09-29T17:21:17+10:00</modifyDate>
             19  </CustomerDetails>
             20  </CustomerList>';
             21  end;
             22  /
             
            PL/SQL procedure successfully completed
             
            SQL> select xmlquery('max(
              2   for $i in /CustomerList/CustomerDetails/modifyDate
              3   return xs:dateTime($i)
              4  )'
              5  passing xmltype(:xmldoc)
              6  returning content
              7  )
              8  from dual
              9  ;
             
            XMLQUERY('MAX(FOR$IIN/CUSTOMER
            --------------------------------------------------------------------------------
             2011-09-29T17:21:17.000000+10:00
             
            • 3. Re: How to get the Max value of elements of type datetime
              tsuji
              Suppose the data contain multiple timezone and that you might want to find the max datetime value with a uniform timezone reference (such as your system's), then you can do this.
              (: suppose $doc hold the document containing the shown fragment... :)
              (: this variable max_dt contains then the max date time in the format of your uniform implicit timezone :)
              let $max_dt := max($doc//CustomerList/CustomerDetails/adjust-dateTime-to-timezone(xs:dateTime(modifyDate)))
              • 4. Re: How to get the Max value of elements of type datetime
                user6736659
                Yes. It worked. Thanks a lot