Oracle PL/SQL Tutorial/XML/existsNode

Call existsNode() function from xmltype data

   <source lang="sql">

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></source>

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>

Find rows that contain the ROWSET/ROW node

   <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> 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></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>