Figure describes the syntax for SQL function existsNode. If so, the function returns 1; otherwise, it returns 0. Therefore, existsNode will return 1 for that XPath. If existsNode is called with an XPath expression that locates no nodes, the function returns 0. Function existsNode can be used in queries, and it can be used to create function-based indexes to speed up evaluation of queries.
The syntax is described in Figure The XPath argument must target a node set. The extracted nodes can be element, attribute, or text nodes. If multiple text nodes are referenced in the XPath expression, the text nodes are collapsed into a single text node value. Namespace can be used to supply namespace information for prefixes in the XPath expression.
It can contain a set of nodes or simple scalar data. A text node is considered an instance of XMLType. In other words, the following expression returns an XMLtype instance even though the instance may contain only text: Not having the text node test would produce an XML fragment.
You can use the index mechanism to identify individual elements in case of repeated elements in an XML document. If you have an XML document such as that in Example , then you can use: SQL function extract can be used in a number of ways. Function extractValue unescapes any encoded entities; method getStringVal returns the data with entity encoding intact.
For documents based on XML schema, if Oracle Database can infer the type of the return value, then a scalar value of the appropriate type is returned. Figure describes the extractValue syntax. With XML schema-based content, extractValue returns the underlying datatype in most cases. CLOB values are returned directly. This can help maintain consistency between different queries regardless of whether the queries can be rewritten.
You can use it in place of extract. If the node at path has only one child and that child is a text node, then you can leave the text test off of the XPath argument: If not, an error is raised if you leave off text. SQL function extractValue has the same syntax as function extract. It cannot return XML nodes or mixed content. An error is raised if extractValue cannot return a scalar value.
If the length is greater than 4K, a run-time error is raised. If XML schema information is available at query compile time, then the datatype of the returned value is based on the XML schema information. If the XPath argument identifies a node, then the node must have exactly one text child or an errror is raised.
The text child is returned. For example, this expression extracts the text child of the Reference node: A local XMLType instance is used to store transient data. Abel Counter to Counter 1 row selected.
Bell Air Mail 1 row selected. McEwen 9 Ellen S. Abel 4 Sarah J. Bell 13 William M. Smith 7 4 rows selected. The result may be a set of nodes, a singleton node, or a text value.
You can determine whether the result is a fragment using the isFragment method on the XMLType instance. You cannot insert fragments into XMLType columns. You can, however, query further on the fragment using the various XMLType functions. This can be created in any of the following ways: This is also called partial updating. These SQL functions are described in the following sections: In particular, do not use function updateXML to insert or delete XML data by replacing a parent node in its entirety; this will work, but it is less efficient than using one of the other functions, which perform more localized updates.
These are all pure functions, without side effects. Each of these functions can be used on XML documents that are either schema-based or non-schema-based. In the case of schema-based XML data, these SQL functions perform partial validation on the result, and, where appropriate, argument values are also checked for compatibility with the XML schema. An error is never raised if no nodes are targeted, but updating SQL functions may raise an error if an XPath-expression argument targets inappropriate nodes, such as attribute nodes or text nodes.
The XML document that is the target of the update can be schema-based or non-schema-based. A copy of the input XMLType instance is modified and returned; the original data is unaffected. Function updateXML has the following parameters in order: One or more pairs of xpath and replacement parameters: These can be nodes of any kind. If xpath matches an empty sequence of nodes, then no replacement is done; target-data is returned unchanged and no error is raised.
The datatype of replacement must correspond to the data to be replaced. SQL function updateXML can be used to replace existing elements, attributes, and other nodes with new values. It is not an efficient way to insert new nodes or delete existing ones; you can only perform insertions and deletions with updateXML by using it to replace the entire node that is parent of the node to be inserted or deleted.
Figure illustrates the syntax. The entire document is updated, not just the part that is selected. The type and namespace properties of the element are retained. If you update an attribute value to NULL, the value appears as the empty string. If you update the text node of an element to NULL, the content text of the element is removed; the element itself remains, but is empty.
The Description element and the Quantity attribute of the LineItem element whose Part element has attribute Id value The order of updates is determined by the order of the XPath expressions in left-to-right order. Each successive XPath works on the result of the previous XPath update. When you update a text node inside an element to NULL, you remove that text node from the element. Furthermore, text nodes can appear only inside simpleType elements when DOM fidelity is not preserved, since there is no positional descriptor with which to store mixed content.
When you update an attribute node to NULL, you remove the attribute from the element. If particular conditions are met, then the function call is rewritten to update the object-relational columns directly with the values.
The XML document that is the target of the insertion can be schema-based or non-schema-based. Function insertChildXML has the following parameters in order: If parent-xpath matches an empty sequence of element nodes, then no insertion is done; target-data is returned unchanged and no error is raised.
If parent-xpath does not match a sequence of element nodes in particular, if parent-xpath matches one or more attribute or text nodes , then an error is raised. The at-sign is not part of the actual attribute name, but serves in the argument to indicate that child-name refers to an attribute. If one or more elements are being inserted, then this is of datatype XMLType, and it contains element nodes.
Each of the top-level element nodes in child-data must have the same name tag as child-name or else an error is raised. If an attribute of the same name already exists at the insertion location, then an error is raised. XML data child-data is inserted as one or more child elements, or a single child attribute, under each of the parent elements located at parent-xpath; the result is returned. If child-name is NULL, then an error is raised.
If child-data is NULL, then: If child-name names an element, then no insertion is done; target-data is returned unchanged. Figure shows the syntax. For example, if the schema constrains child elements named child-name to be the first child elements of a parent-xpath, then the insertion takes this into account.
Similarly, if the child-name or child-data argument is inappropriate for an associated schema, then an error is raised. If the parent element does not yet have a child corresponding in name and kind to child-name and if such a child is permitted by the associated XML schema, if any , then child-data is inserted as new child elements, or a new attribute value, named child-name.
If the parent element already has a child attribute named child-name without the at-sign , then an error is raised. If the parent element already has a child element named child-name and if more than one child element is permitted by the associated XML schema, if any , then child-data is inserted so that its elements become the last child elements named child-name. If XML data to be updated is XML schema-based and it refers to a namespace, then the data to be inserted must also refer to the same namespace; otherwise, an error will be raised because the inserted data does not conform to the XML schema.
For example, if the data in Example used the namespace films. Example Inserting an Element that Uses a Namespace This example is the same as Example , except that the LineItem element to be inserted refers to a namespace. This assumes that the XML schema requires a namespace for this element. Note that this use of namespaces is different from the use of a namespace argument to function insertChildXML — namespaces supplied in that optional argument apply only to the XPath argument, not to the content to be inserted.
Function insertXMLbefore has the following parameters in order: