Oracle UpdateXML with ExistNode - Example - Part 2

Our introduction post to UpdateXML contained a basic example that explained how to use UpdateXML on XMLType Column in Oracle. That is an eye-opener to a beginner who thinks this is a complex function to use in Oracle.

We continue our UpdateXML explanations with some more examples in this post.  Before we start to explain some more features inside UpdateXML, let us add some more rows into our ORDERS table. Run the following SQL statements, that will prepare the test data for us

TRUNCATE TABLE ORDERS  

/
INSERT INTO ORDERS VALUES (1, '<ORDER><PRODUCT TYPE="HandyCam">Sony SX43 Handycam Silver</PRODUCT><PRICE>400</PRICE><RATING>4</RATING></ORDER>')

/
INSERT INTO ORDERS VALUES (2, '<ORDER><PRODUCT TYPE="Digital Camera">CANON EOS 1000D Digital SLR Twin Lens Kit</PRODUCT><PRICE>743</PRICE><RATING>5</RATING></ORDER>')

/
INSERT INTO ORDERS VALUES (3, '<ORDER><PRODUCT TYPE="Digital Camera">CANON IXUS 130 IS Digital Camera Black</PRODUCT><PRICE>285</PRICE><RATING>5</RATING></ORDER>')

/
INSERT INTO ORDERS VALUES (4, '<ORDER><PRODUCT TYPE="Digital Camera">Canon EOS 7D Super Kit</PRODUCT><PRICE>3000</PRICE><RATING>4.5</RATING></ORDER>')

/
INSERT INTO ORDERS VALUES (5, '<ORDER><PRODUCT TYPE="HandyCam">SONY XR150 HD Handycam</PRODUCT><PRICE>888</PRICE><RATING>3.5</RATING></ORDER>')

The objective is to change all Digital Camera model's rating to 4 and do not touch any HandyCam rows. This would mean we have to detect the presence of a TYPE attribute with value "Digital Camera" before running the UpdateXML to update the rating value. i.e. we expect three rows to get updated. To do this, we use a function existsNode in addition to UpdateXML. An example of such an usage is provided below;

UPDATE ORDERS A
   SET A.ORDERDATA = UPDATEXML(A.ORDERDATA, '/ORDER/RATING/text()', '4') /* Use Update XML to update RATING */
 WHERE EXISTSNODE(A.ORDERDATA, '/ORDER/PRODUCT/@TYPE="Digital Camera"') = 1 /* Update only those rows where the TYPE attribute of PRODUCT is "Digital Camera" */

This SQL updates three rows and if you query the table contents after the update, the data should be as shown below

<ORDER><PRODUCT TYPE="HandyCam">Sony SX43 Handycam Silver</PRODUCT><PRICE>400</PRICE><RATING>4</RATING></ORDER>

<ORDER><PRODUCT TYPE="Digital Camera">CANON EOS 1000D Digital SLR Twin Lens Kit</PRODUCT><PRICE>743</PRICE><RATING>4</RATING></ORDER>

<ORDER><PRODUCT TYPE="Digital Camera">CANON IXUS 130 IS Digital Camera Black</PRODUCT><PRICE>285</PRICE><RATING>4</RATING></ORDER>

<ORDER><PRODUCT TYPE="Digital Camera">Canon EOS 7D Super Kit</PRODUCT><PRICE>3000</PRICE><RATING>4</RATING></ORDER>

<ORDER><PRODUCT TYPE="HandyCam">SONY XR150 HD Handycam</PRODUCT><PRICE>888</PRICE><RATING>3.5</RATING></ORDER>

If you see the output above, you can easily conclude that the UpdateXML updated only selective rows and not all of them. Now, how to change the rating of only those rows  to 5 where PRICE is greater than 500? The SQL provided below tells us how to use UpdateXML in this case:

UPDATE ORDERS A
   SET A.ORDERDATA = UPDATEXML(A.ORDERDATA, '/ORDER/RATING/text()', '5') /* Change rating to 5 */
 WHERE EXISTSNODE(A.ORDERDATA, '/ORDER[PRICE>500]') = 1 /* where PRICE tag is greater than 500 */

Now, EXISTSNODE is very powerful to identify a row before applying the UPDATEXML. In the next post, we will see some complex usages of EXISTSNODE function.Note that EXISTSNODE is deprecated with Oracle 11g and a new function XMLEXISTS is being recommended by Oracle. The function is however supported for backward compatibility.