Oracle PL/SQL Tutorial/XML/existsNode

Call existsNode() function from xmltype data

SQL>
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 d.myValue.existsNode("/ROWSET") = 1
  4  /
  COUNT(*)
----------
         0
1 row selected.
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

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.

Find rows that contain the ROWSET/ROW node

SQL> CREATE TABLE myTable
  2    (myID            NUMBER PRIMARY KEY,
  3     myValue     XMLTYPE )
  4     XMLTYPE myValue STORE AS CLOB
  5  /
Table created.

SQL>
SQL>
SQL> 
SQL> select myID
  2  from myTable
  3  where existsnode(myValue, "/ROWSET/ROW")  = 1
  4  /
no rows selected
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

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.