Oracle PL/SQL/XML/ExistsNode

Count(*) and existsnode()

   <source lang="sql">
 

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.

 </source>
   
  

existsnode and where clause

   <source lang="sql">
 

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>

 </source>
   
  

existsnode(myValue, "/ROWSET")

   <source lang="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 existsnode(myValue, "/ROWSET") = 1
 4  /
 COUNT(*)

---

        0

1 row selected. SQL> SQL> SQL> drop table myTable; Table dropped.

 </source>
   
  

Oracle XML function ExistsNode.

   <source lang="sql">
 

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.

 </source>