Oracle insertXMLbefore Function Example

After discussing how to use insertChildXML function, in this post let us see how to use insertXMLbefore function with a simple example. To illustrate the usage of insertXMLbefore function, consider the following XML below:

<Laptop>

<Notebooks>
            <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>
            <Notebook>
                        <Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title>
                        <Price>1200</Price>
                        <Rating>5</Rating>
                        <Discount>50</Discount>
            </Notebook>

</Notebooks>
</Laptop>

Let us explain the usage of insertXMLbefore by adding a Notebook element before HP brand. To do this, observe the following SQL below

UPDATE ORDERS A
   SET A.ORDERDATA = insertXMLbefore (A.ORDERDATA, /* column to be updated */
                                    '/Laptop/Notebooks/Notebook[1]', /* find the first Notebook element to insert before */
                                    XMLTYPE('<Notebook><Title Brand="ASUS">Asus N61 16 Notebook </Title><Price>1700</Price></Notebook>'))

This SQL inserts a Notebook element for us before the HP brand..a shortened version of the output is shown below

<Laptop>
  <Notebooks>
    <Notebook>
      <Title Brand="ASUS">Asus N61 16 Notebook </Title>
      <Price>1700</Price>
    </Notebook>
    <Notebook>
      <Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>
      <Price>450</Price>
      <Rating>5</Rating>
    </Notebook>

Now, all the processor for these Notebooks are iCore7 types. I would like to add a tag, "Processor" to all the Notebook elements using insertXMLbefore function. How to do that?  SQL below..

UPDATE ORDERS A
   SET A.ORDERDATA = insertXMLbefore (A.ORDERDATA,
                                    '/Laptop/Notebooks/Notebook/Price',/* Read all matching Price tags */
                                    XMLTYPE('<Processor>iCore7</Processor>')) /* Insert Processor type tag */

The output of this SQL is shown below

<Laptop>
  <Notebooks>
    <Notebook>
      <Title Brand="ASUS">Asus N61 16 Notebook </Title>
      <Processor>iCore7</Processor>
      <Price>1700</Price>
    </Notebook>
    <Notebook>
      <Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>
      <Processor>iCore7</Processor>
      <Price>450</Price>
      <Rating>5</Rating>
    </Notebook>

………..

It is to be noted that we could have accomplished the same task with INSERTCHILDXML function also. But the main difference between insertXMLbefore and InsertChildXML is that the former allows you to position the exact location of the tag inside the XML element.

We will discuss one more function, AppendChildXML in our next post with an example.