www.oradev.com
  Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  Oracle XML functions

Functions

Oracle (var)char functions
Instr function
Number format
Kill oracle session
to_date function
Oracle sysdate
Oracle substr
How to use the DECODE statement
How to use the CASE statement
How to use the NVL statement
Using XML functions
Oracle date format
Oracle numeric functions
Oracle date functions
Pl sql trim


  OraDev.com

XML Functions

Oracle provides XML functions to operate on or return XML documents or fragments.
Here is a list of all the XML functions (in version 10.2):
Function Usage
Description
APPENDCHILDXML APPENDCHILDXML(XMLTYPE_instance,XPath_string,value_expr) or
APPENDCHILDXML(XMLTYPE_instance,XPath_string,value_expr,namespace_string)
APPENDCHILDXML appends a user-supplied value onto the target XML as the child of the node indicated by an XPath expression. XMLType_instance is an instance of XMLType.
The XPath_string is an Xpath expression indicating one or more nodes onto which one or more child nodes are to be appended. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.
The value_expr specifies one or more nodes of XMLType. It must resolve to a string.
The optional namespace_string provides namespace information for the XPath_string. This parameter must be of type VARCHAR2.
DELETEXML DELETEXML(XMLTYPE_instance, XPath_string) or
DELETEXML(XMLTYPE_instance, XPath_string, namespace_string)
DELETEXML deletes the node or nodes matched by the XPath expression in the target XML.
XMLType_instance is an instance of XMLType.
The XPath_string is an Xpath expression indicating one or more nodes that are to be deleted. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node. Any child nodes of the nodes specified by XPath_string are also deleted.
The optional namespace_string provides namespace information for the XPath_string. This parameter must be of type VARCHAR2.
DEPTH DEPTH(correlation_integer)
DEPTH is an ancillary function used only with the UNDER_PATH and EQUALS_PATH conditions. It returns the number of levels in the path specified by the UNDER_PATH condition with the same correlation variable. The correlation_integer can be any NUMBER integer. Use it to correlate this ancillary function with its primary condition if the statement contains multiple primary conditions. Values less than 1 are treated as 1.
EXTRACT (XML) EXTRACT(XMLTYPE_instance,XPath_string) or
EXTRACT(XMLTYPE_instance,XPath_string, namespace_string)
EXTRACT (XML) is similar to the EXISTSNODE function. It applies a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node. The optional namespace_string must resolve to a VARCHAR2 value that specifies a default mapping or namespace mapping for prefixes, which Oracle Database uses when evaluating the XPath expression(s).
EXISTSNODE EXISTSNODE(XMLTYPE_instance,XPath_string) or
EXISTSNODE(XMLTYPE_instance,XPath_string, namespace_string)
EXISTSNODE determines whether traversal of an XML document using a specified path results in any nodes. It takes as arguments the XMLType instance containing an XML document and a VARCHAR2 XPath string designating a path. The optional namespace_string must resolve to a VARCHAR2 value that specifies a default mapping or namespace mapping for prefixes, which Oracle Database uses when evaluating the XPath expression(s).
The namespace_string argument defaults to the namespace of the root element. If you refer to any subelement in Xpath_string, then you must specify namespace_string, and you must specify the "who" prefix in both of these arguments.
EXTRACTVALUE EXTRACTVALUE(XMLTYPE_instance,XPath_string) or
EXTRACTVALUE(XMLTYPE_instance,XPath_string, namespace_string)
The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node. The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.
INSERTCHILDXML INSERTCHILDXML(XMLTYPE_instance,XPath_string, child_expr, value_expr) or
INSERTCHILDXML(XMLTYPE_instance,XPath_string, child_expr, value_expr, namespace_string)
INSERTCHILDXML inserts a user-supplied value into the target XML at the node indicated by the XPath expression. Compare this function with INSERTXMLBEFORE.
XMLType_instance is an instance of XMLType.
The XPath_string is an Xpath expression indicating one or more nodes into which the one or more child nodes are to be inserted. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.
The child_expr specifies the one or more element or attribute nodes to be inserted.
The value_expr is an fragment of XMLType that specifies one or more notes being inserted. It must resolve to a string.
The optional namespace_string provides namespace information for the XPath_string. This parameter must be of type VARCHAR2.
INSERTXMLBEFORE INSERTXMLBEFORE(XMLTYPE_instance,XPath_string, value_expr) or
INSERTXMLBEFORE(XMLTYPE_instance,XPath_string, value_expr, namespace_string)
INSERTXMLBEFORE inserts a user-supplied value into the target XML before the node indicated by the XPath expression. Compare this function with INSERTCHILDXML.
XMLType_instance is an instance of XMLType.
The XPath_string is an Xpath expression indicating one or more nodes into which one or more child nodes are to be inserted. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.
The value_expr is a fragment of XMLType that defines one or more nodes being inserted and their position within the parent node. It must resolve to a string.
The optional namespace_string provides namespace information for the XPath_string. This parameter must be of type VARCHAR2.
PATH PATH(correlation_integer)
PATH is an ancillary function used only with the UNDER_PATH and EQUALS_PATH conditions. It returns the relative path that leads to the resource specified in the parent condition.
The correlation_integer can be any NUMBER integer and is used to correlate this ancillary function with its primary condition. Values less than 1 are treated as 1.
SYS_DBURIGEN SYS_DBURIGEN(column,text)
SYS_DBURIGen takes as its argument one or more columns or attributes, and optionally a rowid, and generates a URL of datatype DBURIType to a particular column or row object. You can then use the URL to retrieve an XML document from the database.
SYS_XMLAGG SYS_XMLAGG(expr) or
SYS_XMLAGG(expr,fmt)
SYS_XMLAgg aggregates all of the XML documents or fragments represented by expr and produces a single XML document. It adds a new enclosing element with a default name ROWSET. If you want to format the XML document differently, then specify fmt, which is an instance of the XMLFormat object.
SYS_XMLGEN SYS_XMLGEN(expr) or
SYS_XMLGEN(expr,fmt)
SYS_XMLGen takes an expression that evaluates to a particular row and column of the database, and returns an instance of type XMLType containing an XML document. The expr can be a scalar value, a user-defined type, or an XMLType instance.
If expr is a scalar value, then the function returns an XML element containing the scalar value.
If expr is a type, then the function maps the user-defined type attributes to XML elements.
If expr is an XMLType instance, then the function encloses the document in an XML element whose default tag name is ROW.
By default the elements of the XML document match the elements of expr. For example, if expr resolves to a column name, then the enclosing XML element will be the same column name. If you want to format the XML document differently, then specify fmt, which is an instance of the XMLFormat object.
UPDATEXML UPDATEXML(XMLTYPE_instance,XPath_string, value_expr) or
UPDATEXML(XMLTYPE_instance,XPath_string, value_expr, namespace_string)
UPDATEXML takes as arguments an XMLType instance and an XPath-value pair and returns an XMLType instance with the updated value. If XPath_string is an XML element, then the corresponding value_expr must be an XMLType instance. If XPath_string is an attribute or text node, then the value_expr can be any scalar datatype. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node. The datatypes of the target of each XPath_string and its corresponding value_expr must match. The optional namespace_string must resolve to a VARCHAR2 value that specifies a default mapping or namespace mapping for prefixes, which Oracle Database uses when evaluating the XPath expression(s).
XMLAGG XMLAGG(XMLTYPE_instance) or
XMLAGG(XMLTYPE_instance, order_by_clause)
XMLAgg is an aggregate function. It takes a collection of XML fragments and returns an aggregated XML document. Any arguments that return null are dropped from the result.
XMLAgg is similar to SYS_XMLAgg except that XMLAgg returns a collection of nodes but it does not accept formatting using the XMLFormat object. Also, XMLAgg does not enclose the output in an element tag as does SYS_XMLAgg.
Within the order_by_clause, Oracle Database does not interpret number literals as column positions, as it does in other uses of this clause, but simply as number literals.
XMLCDATA XMLCDATA(value_expr)
XMLCData generates a CDATA section by evaluating value_expr. The value_expr must resolve to a string. The value returned by the function takes the following form:

If the resulting value is not a valid XML CDATA section, then the function returns an error.The following conditions apply to XMLCData:
The value_expr cannot contain the substring ]]>.
If value_expr evaluates to null, then the function returns null.
XMLCOLATTVAL XMLCOLATTVAL(value_expr AS c_alias) or
XMLCOLATTVAL(value_expr AS c_alias, value_expr AS c_alias,..)
XMLColAttVal creates an XML fragment and then expands the resulting XML so that each XML fragment has the name column with the attribute name. You can use the AS c_alias clause to change the value of the name attribute to something other than the column name. You must specify a value for value_expr. If value_expr is null, then no element is returned. Restriction on XMLColAttVal: You cannot specify an object type column for value_expr.
XMLCOMMENT XMLCOMMENT(value_expr)
XMLComment generates an XML comment using an evaluated result of value_expr. The value_expr must resolve to a string. It cannot contain two consecutive dashes (hyphens). The value returned by the function takes the following form:
<!--string-->
If value_expr resolves to null, then the function returns null.
XMLCONCAT XMLCONCAT(XMLTYPE_instance) or
XMLCONCAT(XMLTYPE_instance, XMLTYPE_instance, ...)
XMLConcat takes as input a series of XMLType instances, concatenates the series of elements for each row, and returns the concatenated series. XMLConcat is the inverse of XMLSequence.
Null expressions are dropped from the result. If all the value expressions are null, then the function returns null.
XMLFOREST XMLFOREST(value_expr AS c_alias) or
XMLFOREST(value_expr AS c_alias, value_expr AS c_alias,..)
XMLForest converts each of its argument parameters to XML, and then returns an XML fragment that is the concatenation of these converted arguments.
If value_expr is a scalar expression, then you can omit the AS clause, and Oracle Database uses the column name as the element name.
If value_expr is an object type or collection, then the AS clause is mandatory, and Oracle uses the specified c_alias as the enclosing tag. The c_alias can be up to 4000 characters.
If value_expr is null, then no element is created for that value_expr.
XMLPARSE XMLPARSE(DOCUMENT, value_expr) or
XMLPARSE(DOCUMENT, value_expr WELLFORMED) or
XMLPARSE(CONTENT, value_expr) or
XMLPARSE(CONTENT, value_expr WELLFORMED)
XMLParse parses and generates an XML instance from the evaluated result of value_expr. The value_expr must resolve to a string. If value_expr resolves to null, then the function returns null.
If you specify DOCUMENT, then value_expr must resolve to a singly rooted XML document.
If you specify CONTENT, then value_expr must resolve to a valid XML value.
When you specify WELLFORMED, you are guaranteeing that value_expr resolves to a well-formed XML document, so the database does not perform validity checks to ensure that the input is well formed.
XMLPI XMLPI(identifier) or
XMLPI(NAME identifier) or
XMLPI(NAME identifier , value_expr)
XMLPI generates an XML processing instruction using identifier and optionally the evaluated result of value_expr. A processing instruction is commonly used to provide to an application information that is associated with all or part of an XML document. The application uses the processing instruction to determine how best to process the XML document.
XMLQUERY XMLQUERY(XQuery_string RETURNING CONTENT) or
XMLQUERY(XQuery_string XML_passing_clause RETURNING CONTENT)
XMLQUERY lets you query XML data in SQL statements. It takes an XQuery expression as a string literal, an optional context item, and other bind variables and returns the result of evaluating the XQuery expression using these input values.
XQuery_string is a complete XQuery expression, including prolog.
The expr in the XML_passing_clause is an expression returning an XMLType that is used as the context for evaluating the XQuery expression. You can specify only one expr in the PASSING clause without an identifier. The result of evaluating each expr is bound to the corresponding identifier in the XQuery_string. If any expr that is not followed by an AS clause, then the result of evaluating that expression is used as the context item for evaluating the XQuery_string.
RETURNING CONTENT indicates that the result from the XQuery evaluation is either an XML 1.0 document or a document fragment conforming to the XML 1.0 semantics.
XMLROOT XMLROOT(...)
XMLROOT lets you create a new XML value by providing version and standalone properties in the XML root information (prolog) of an existing XML value. If the value_expr already has a prolog, then the database returns an error. If the input is null, then the function returns null.
XMLSEQUENCE XMLSEQUENCE(XMLTYPE_instance) or
XMLSEQUENCE(sys_refcursor_instance) or
XMLSEQUENCE(sys_refcursor_instance, fmt)
XMLSequence has two forms:
The first form takes as input an XMLType instance and returns a varray of the top-level nodes in the XMLType. This form is effectively superseded by the SQL/XML standard function XMLTable, which provides for more readable SQL code. Prior to Oracle Database 10g Release 2, XMLSequence was used with SQL function TABLE to do some of what can now be done better with the XMLTable function.
The second form takes as input a REFCURSOR instance, with an optional instance of the XMLFormat object, and returns as an XMLSequence type an XML document for each row of the cursor.
Because XMLSequence returns a collection of XMLType, you can use this function in a TABLE clause to unnest the collection values into multiple rows, which can in turn be further processed in the SQL query.
XMLSERIALIZE XMLSERIALIZE(DOCUMENT, value_expr) or
XMLSERIALIZE(DOCUMENT, value_expr AS datatype) or
XMLSERIALIZE(CONTENT, value_expr) or
XMLSERIALIZE(CONTENT, value_expr AS datatype)
XMLSerialize creates a string or LOB containing the contents of value_expr.
If you specify DOCUMENT, then the value_expr must be a valid XML document.
If you specify CONTENT, then the value_expr need not be a singly rooted XML document. However it must be valid XML content.
The datatype specified can be a string type (VARCHAR2 or VARCHAR, but not NVARCHAR or NVARCHAR2) or CLOB . The default is CLOB.
XMLTABLE XMLTABLE(XML_namespaces_clause, XQuery_string XMLTABLE_options) XMLTABLE(XQuery_string XMLTABLE_options)
XMLTable maps the result of an XQuery evaluation into relational rows and columns. You can query the result returned by the function as a virtual relational table using SQL.
XMLTRANSFORM XMLTRANSFORM(XMLTYPE_instance,XMLTYPE_instance)
XMLTransform takes as arguments an XMLType instance and an XSL style sheet, which is itself a form of XMLType instance. It applies the style sheet to the instance and returns an XMLType.
This function is useful for organizing data according to a style sheet as you are retrieving it from the database.