CODEWITHSUNDEEP
oracle-databasexquery
jhon.smith
jhon.smith

Reputation: 2043

Xquery casting to date doesnt return rows

I have a table called test in Oracle XDB with one row.

SQL> select * from test;

SYS_NC_ROWINFO$
---------------------------
<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="">
  <location>
    <joiningDate id="onsite">
      <hireDate>2012-06-18</hireDate>
    </joiningDate>
    <joiningDate id="offshore">
      <hireDate>2011-07-8</hireDate>
    </joiningDate>
  </location>
</Employee>

Elapsed: 00:00:00.00

I also have a index like

CREATE INDEX test_xml_index ON test (OBJECT_VALUE)
  INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PATH TABLE test_dates_tab');

BEGIN
  DBMS_XMLINDEX.registerParameter(
    'myprop',
    'ADD_GROUP GROUP test_dates
       XMLTable test_dates_tab ''/Employee''
       XMLNAMESPACES (''http://www.w3.org/2001/XMLSchema-instance'' AS "xsi")                    
         COLUMNS onsite      date  PATH ''.//*[@id="onsite"]/hireDate/text()''               
       ');
END;
/

why is my query not returning a blank value ?

SQL> select x.*
  2          from test t,
  3             xmltable(
  4               xmlnamespaces(
  5                 'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
  6               ),
  7               'for $d in /Employee/location
  8                         where $d/joiningDate[@id="onsite"]/hireDate/text() = xs:date("2012-06-18")
  9                  return $d'
 10               passing t.object_value
 11                 columns
 12                 hiredate  date path 'hireDate'
 13             ) as x
 14
SQL> /

HIREDATE
--------------------

Got it with your help Jens

select x.*
        from test t,
           xmltable(
             xmlnamespaces(
               'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
             ),
             'for $d in /Employee/location
                       where $d/joiningDate[@id="onsite"]/hireDate/text() = xs:date("2012-06-18")
                return $d'
             passing t.object_value
               columns
               hiredate  date path 'joiningDate[@id="onsite"]/hireDate'
           ) as x

Upvotes: 2

Views: 157

Answers (1)

Jens Erat
Jens Erat

Reputation: 38682

There's a typo in your XQuery: the element is named <hireDate/>, but your axis steps says hiredate.

Upvotes: 2

Related Questions