Using UpdateXML in XMLTYPE Column Example Part 1

In this post, we will present a simple example that explains how to use UpdateXML function in Oracle. As we move on, we will see some complex usages of using UpdateXML function.

To run the UpdateXML examples, you need to have a table with XMLTYPE field. Create the following table and push the data as per the INSERT script below

CREATE TABLE ORDERS
(ORDERID NUMBER,
ORDERDATA XMLTYPE)

INSERT INTO ORDERS
VALUES
  (1,
   '<ORDER><PRODUCT>NIKON 16.2MP, DSLR SINGLE VR KIT</PRODUCT><PRICE>1693</PRICE><RATING>5</RATING></ORDER>')

We have a simple table with one row and an order XML that has a Nikon DSLR Model, with Price and Rating. So simple to get started with UPDATEXML. The objective for us would be to write an SQL that changes the price from 1693 to 1000…so nice is it not?..To change the price, the SQL to be used is shown below

UPDATE ORDERS A1 SET A1.ORDERDATA=UPDATEXML(A1.ORDERDATA,'ORDER/PRICE','1000')
WHERE A1.ORDERID=1

Wait, is that all? When you requery the table after update, you see the content on ORDERDATA column as shown below

<ORDER><PRODUCT>NIKON 16.2MP, DSLR SINGLE VR KIT</PRODUCT><RATING>5</RATING>1000</ORDER>

The Price tag has disappeared completely..Our objective was to update the value of PRICE tag and we have ended up removing the tag.. Rollback your update and run an update as shown below

UPDATE ORDERS A1 SET A1.ORDERDATA=UPDATEXML(A1.ORDERDATA,'ORDER/PRICE/text()','1000')
WHERE A1.ORDERID=1

Now, when you query the row that we updated, you will be able to see the output as shown below;

<ORDER><PRODUCT>NIKON 16.2MP, DSLR SINGLE VR KIT</PRODUCT><PRICE>1000</PRICE><RATING>5</RATING></ORDER>

This is the output we were after..ok, since we reduced the price, I would also like to reduce the rating from 5 to 4. How to do this in a single SQL query? Can we update more than one XML tag in a single UPDATEXML? Yes, it is possible..Refer to the SQL below that explains how to update more than one tag using UpdateXML function

UPDATE ORDERS A1 SET A1.ORDERDATA=UPDATEXML(A1.ORDERDATA,'ORDER/PRICE/text()','1000','ORDER/RATING/text()','4')
WHERE A1.ORDERID=1

This single SQL updates both the tags, and the result of this SQL is shown below

<ORDER><PRODUCT>NIKON 16.2MP, DSLR SINGLE VR KIT</PRODUCT><PRICE>1000</PRICE><RATING>4</RATING></ORDER>

This would have given you a start on how to use UPDATEXML on XMLTYPE columns. These examples are just basic ones. As we move on in this new series, we will see some interesting examples for UpdateXML..