INSERTCHILDXML

Purpose

This function inserts a specified XML fragment into a target XML document. The insertion position is determined by the nodes indicated by the XPath expression.

Syntax

INSERTCHILDXML( XMLType_instance, XPath_string, child_expr, value_expr [, namespace_string ])

Parameters

Return type

XMLType

Examples

• Use the INSERTCHILDXML function to insert a child node into a target XML document. In this example, the target XML document contains the <bookstore> node and its child nodes. The XPath expression /bookstore indicates that the child node should be inserted into the <bookstore> node. The name of the child node to be inserted is price, and its content is an XMLType instance that represents <price>99.9</price>. The query result is renamed to result using the AS keyword. Finally, the result is retrieved from the dual table.

  SELECT INSERTCHILDXML(
          XMLtype('<bookstore>
                      <book att="old"></book>
                      <author>carrot</author>
                    </bookstore>'),
          '/bookstore',
          'price',
          XMLtype('<price>99.9</price>')
        ) AS result
  FROM dual;
  

  The result is as follows:

  +-----------------------------------------------------------------------------------------------+
  | RESULT                                                                                        |
  +-----------------------------------------------------------------------------------------------+
  | <bookstore>
    <book att="old"/>
    <author>carrot</author>
    <price>99.9</price>
  </bookstore>
  |
  +-----------------------------------------------------------------------------------------------+
  1 row in set
  

• Insert a node.

  SELECT
    INSERTCHILDXML(
      xmltype(
        '<bookstore>
          <book att="old"></book>
          <author>carrot</author>
        </bookstore>'
      ),
      '/bookstore/book',
      'a',
      xmltype('<a><b/></a>')
    )
  FROM dual;
  

  The result is as follows:

  +----------------------------------------------------------------------------------------------------------------------------------------------+
  | INSERTCHILDXML(XMLTYPE('<BOOKSTORE><BOOKATT="OLD"></BOOK><AUTHOR>CARROT</AUTHOR></BOOKSTORE>'),'/BOOKSTORE/BOOK','A',XMLTYPE('<A><B/></A>')) |
  +----------------------------------------------------------------------------------------------------------------------------------------------+
  | <bookstore>
    <book att="old">
      <a>
        <b/>
      </a>
    </book>
    <author>carrot</author>
  </bookstore>
                                  |
  +----------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

• If the XPath expression returns multiple nodes, all of them will be inserted.

  SELECT
    INSERTCHILDXML(
      xmltype(
        '<bookstore>
          <book att="old"></book>
          <book></book>
          <author>carrot</author>
        </bookstore>'
      ),
      '/bookstore/book',
      'price',
      xmltype('<price>99.9</price>')
    )
  FROM dual;
  

  The result is as follows:

  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | INSERTCHILDXML(XMLTYPE('<BOOKSTORE><BOOKATT="OLD"></BOOK><BOOK></BOOK><AUTHOR>CARROT</AUTHOR></BOOKSTORE>'),'/BOOKSTORE/BOOK','PRICE',XMLTYPE('<PRICE>99.9</PRICE>')) |
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | <bookstore>
    <book att="old">
      <price>99.9</price>
    </book>
    <book>
      <price>99.9</price>
    </book>
    <author>carrot</author>
  </bookstore>
                    |
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

• Insert an attribute.

  SELECT
    INSERTCHILDXML(
      xmltype(
        '<bookstore>
          <book att="old"></book>
          <author>carrot</author>
        </bookstore>'
      ),
      '/bookstore/book',
      '@att2',
      'abc'
    )
  FROM dual;
  

  The result is as follows:

  +---------------------------------------------------------------------------------------------------------------------------------+
  | INSERTCHILDXML(XMLTYPE('<BOOKSTORE><BOOKATT="OLD"></BOOK><AUTHOR>CARROT</AUTHOR></BOOKSTORE>'),'/BOOKSTORE/BOOK','@ATT2','ABC') |
  +---------------------------------------------------------------------------------------------------------------------------------+
  | <bookstore>
    <book att="old" att2="abc"/>
    <author>carrot</author>
  </bookstore>
                                                |
  +---------------------------------------------------------------------------------------------------------------------------------+
  1 row in set