Oracle PL/SQL/XML/ExistsNode

Count(*) and existsnode()

  
SQL> CREATE TABLE myTable
  2  (id  NUMBER PRIMARY KEY
  3  ,doc XMLType NOT NULL)
  4  XMLTYPE doc STORE AS CLOB
  5  /
Table created.
SQL>
SQL> select COUNT(*) from myTable
  2  where existsnode(doc, "/message/greeting") = 1
  3  /
  COUNT(*)
----------
         0
1 row selected.
SQL> drop table myTable;
Table dropped.

existsnode and where clause

  
SQL> create table myTable of xmltype;
Table created.
SQL>
SQL> insert into myTable values(XMLTYPE("
  2    <customer>
  3     <name>Chris</name>
  4      <telephone>123 555-1234</telephone>
  5    </customer>"))
  6  /
1 row created.
SQL>
SQL> select * from myTable;

SYS_NC_ROWINFO$
------------------------------------------------------
  <customer>
   <name>Chris</name>
    <telephone>123 555-1234</telephone>
  </
1 row selected.
SQL>
SQL> update myTable c
  2  set value(c) = updateXML(value(c), "/customer/name/text()","new value")
  3  /
1 row updated.
SQL>
SQL>
SQL> select extractvalue(value(c),"/customer/telephone")
  2  from myTable c
  3  where existsnode(value(c),"/customer/name = "Chris"") = 1
  4  /
no rows selected
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

existsnode(myValue, "/ROWSET")

  
SQL> CREATE TABLE myTable
  2    (myID            NUMBER PRIMARY KEY,
  3     myValue     XMLTYPE )
  4     XMLTYPE myValue STORE AS CLOB
  5  /
Table created.
SQL>
SQL> select COUNT(*)
  2  from myTable d
  3  where existsnode(myValue, "/ROWSET") = 1
  4  /
  COUNT(*)
----------
         0
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

Oracle XML function ExistsNode.

  
SQL>
SQL> CREATE TABLE myTable(
  2      id  NUMBER PRIMARY KEY
  3      ,doc XMLType NOT NULL
  4  )
  5  XMLTYPE doc STORE AS CLOB
  6  /
Table created.
SQL>
SQL> -- Demo the selfish style of invocation
SQL> select COUNT(*)
  2  from myTable x
  3  where x.doc.existsnode("/message/greeting") = 1
  4  /
  COUNT(*)
----------
         0
1 row selected.
SQL>
SQL> drop table myTable;
Table dropped.