INSERTCHILDXML|V4.3.1|OceanBase Database| docs|Distributed Database

Purpose

INSERTCHILDXML() inserts the specified XML fragment into nodes indicated by an XPath expression in the target XML document.

Syntax

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

Parameters

Parameter	Description
XMLType_instance	The `XMLType` instance that represents the target XML document.
XPath_string	The XPath expression that indicates one or more target nodes into which one or more child nodes are to be inserted. You can use an absolute path (starting with a slash) or a relative path (with the initial slash omitted). If the initial slash is omitted, the context of the relative path is the root node by default.
child_expr	The one or more element or attribute nodes to be inserted.
value_expr	The `XMLType` fragment that specifies one or more nodes to be inserted. It must resolve to a string or `XMLType` data.
namespace_string	The namespace information for `XPath_string`. This parameter is optional.

Note

If target parent nodes have other namespaces but child nodes to be inserted do not, all the child nodes need to be traversed and configured with an empty namespace.

Return type

The return type is XMLType.

Examples

Call INSERTCHILDXML() to insert a child node into the target XML document, which is the <bookstore> node and its child nodes. The XPath expression is /bookstore, which indicates that the child node is to be inserted into the <bookstore> node. The name of the target child node to be inserted is price. The content of the child node to be inserted is an XMLType instance representing <price>99.9</price>. The query result is renamed as result by 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 return 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 return 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 matches multiple nodes, the specified child node is inserted into each of the nodes.

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 return 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 return 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

Customer Stories

Documentation

INSERTCHILDXML

Purpose

Syntax

Parameters

Note

Return type

Examples