3 Replies Latest reply: Nov 23, 2010 1:54 PM by odie_63 RSS

    xquery with Dates

    760751
      Hi

      I have xml stored in a xmltype table
      <?xml version="1.0"?>                                                           
      <myDate xmlns="http://www.example.com/schema" dateTimeProc="20101028T102806" Num="666" timId="4" procId="1" proces="501">                                                          
      </myDate>                       
                                    
      <?xml version="1.0"?>                                                           
      <myDate xmlns="http://www.example.com/schema" dateTimeProc="20100824T102806" Num="666" timId="4" procId="1" proces="501">                                                          
      </myDate>  
      
      <?xml version="1.0"?>                                                           
      <myDate xmlns="http://www.example.com/schema" dateTimeProc="20101110T102806" Num="666" timId="4" procId="1" proces="501">                                                          
      </myDate>  
      I would like to know whats the xpath expression to get all xml's that are between certain dates something like

      select * from XMLTable
      where existsNode(object_value,'myDate[@dateTimeProc]') between sysdate-120 and sysdate-90

      Edited by: user9243284 on Nov 22, 2010 3:42 AM
        • 1. Re: xquery with Dates
          odie_63
          Hi,

          You didn't give your database version, so I'll assume 11g.

          The format you're using is not very adapted from date comparisons, it would have been easier with the canonical xs:dateTime (YYYY-MM-DDTHH:MI:SS).
          Anyway, you can try :
          SELECT *
          FROM your_table
          WHERE to_date(
                  xmlquery('declare default element namespace "http://www.example.com/schema"; (::)
                            /myDate/@dateTimeProc' 
                            passing object_value returning content) 
                  , 'YYYYMMDD"T"HH24MISS'
                ) BETWEEN sysdate-120 and sysdate-90
          ;
          • 2. Re: xquery with Dates
            Marco Gralike
            I guess it would have been easier via a CAST/XMLCAST to DATE (instead of to_date I mean) ?

            Edited by: Marco Gralike on Nov 22, 2010 9:57 PM
            • 3. Re: xquery with Dates
              odie_63
              I guess it would have been easier via a CAST/XMLCAST to DATE (instead of to_date I mean) ?
              Yes, I first thought the format wouldn't be recognized, but actually it is.
              SQL> SELECT xmlcast(
                2          xmlquery('/dt' passing xmltype('<dt>20101028T102806</dt>') returning content)
                3          as timestamp
                4         ) "timestamp"
                5  FROM dual
                6  ;
               
              timestamp
              -------------------------------------------------
              28/10/10 10:28:06,000000