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 isprice. The content of the child node to be inserted is anXMLTypeinstance representing<price>99.9</price>. The query result is renamed asresultby using theASkeyword. Finally, the result is retrieved from thedualtable.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 setInsert 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 setIf 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 setInsert 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