This discussion is archived
3 Replies Latest reply: Nov 23, 2010 11:54 AM by odie_63 RSS

xquery with Dates

760751 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
     

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points