Oracle insertChildXML Function Example

In this post, we will explain the insertChildXML  function in Oracle with a simple example. We will use the XML provided below :

<Laptop>
            <Notebook>
                        <Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>
                        <Price>450</Price>
            </Notebook>
            <Notebook>
                        <Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title>
                        <Price>800</Price>
            </Notebook>
            <Notebook>
                        <Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title>
                        <Price>700</Price>
            </Notebook>
            <Notebook>
                        <Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title>
                        <Price>1200</Price>
            </Notebook>
</Laptop>

My aim would be to INSERT a new set of "Notebook" element into this XML for another laptop brand. The new tags which we would like to insert is shown separately below

<Notebook>
                        <Title Brand="ASUS">Asus N61 16 Notebook </Title>
                        <Price>1700</Price>
</Notebook>

To easily update the XML for this purpose, we can use the function "insertChildXML" in Oracle. The usage of insertChildXML for this case is illustrated below

UPDATE ORDERS A
   SET A.ORDERDATA = INSERTCHILDXML(A.ORDERDATA, /* The column to be updated, in this case ORDERDATA our XMLTYPE column */
                                    '/Laptop',/* The XPath where the insert has to take place, in this case it is from root 'Laptop' node */
                                    'Notebook', /* The element name that needs to be inserted */
                                    XMLTYPE('<Notebook><Title Brand="ASUS">Asus N61 16 Notebook </Title><Price>1700</Price></Notebook>')) /* data */

The output of this SQL is provided below.(I have skipped some sections of the XML to show only the new Notebook element)

<Laptop><Notebook><Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title></Notebook>……..<Notebook><Title Brand="ASUS">Asus N61 16 Notebook </Title><Price>1700</Price></Notebook></Laptop>

Now, for all the Notebook elements if you want to  include a RATING with a default value of 5, how can this be done using INSERTCHILDXML function? The SQL to do this is provided below

UPDATE ORDERS A
   SET A.ORDERDATA = INSERTCHILDXML(A.ORDERDATA,
                                    '/Laptop/Notebook', /* grab all matching Notebook elements */
                                    'Rating', /* insert a Rating tag under Notebook */
                                    XMLTYPE('<Rating>5</Rating>')) /* set default value for Rating tag */

The output of this SQL is shown below (not all tags are shown)

<Laptop><Notebook><Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title><Price>450</Price><Rating>5</Rating></Notebook><Notebook><Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title><Price>800</Price><Rating>5</Rating></Notebook>………….</Laptop>

Now, I want to add a DISCOUNT tag to "Notebook" elements, where price of the Notebook is greater than 1000..This can be done as shown below

UPDATE ORDERS A
   SET A.ORDERDATA = INSERTCHILDXML(A.ORDERDATA,
                                    '/Laptop/Notebook[Price>1000]', /* Pick only those nodes where Price is greater than 1000 */
                                    'Discount', /* Add discount tag selectively */
                                    XMLTYPE('<Discount>50</Discount>'))

The output of this SQL is shown below..two Notebook elements are shown, one with Discount added as it mets the condition and the other with no discount added

<Notebook><Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title><Price>450</Price><Rating>5</Rating></Notebook>

<Notebook><Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title><Price>1200</Price><Rating>5</Rating><Discount>50</Discount></Notebook>