34
SQLX Functions and Operators

This chapter contains the following sections:

• SQLX Package

• 
   

  See Also: Oracle9i XML Database Developer's Guide - Oracle XML DB Oracle9i SQL Reference

---

SQLX Package

---

Description of SQLX

The SQLX functions supported with this release follow the SQLX standard.

---

Functions and Operators of SQLX

Table 34-1 Summary of Functions and Operators of SQLX

Function/Operator	Description
XMLElement()	Creates an XML Element.
XMLForest()	Creates an XML Fragment from passed-in components.
XMLColAttVal()	Creates an XML fragment and then expands the resulting XML so that each XML fragment has the name "column" with the attribute "name"
ExtractValue()	Takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node.
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.
XMLSequence()	Takes input and returns either a varray of the top-level nodes in the XMLType, or an XMLSequence type an XML document for each row of the cursor.
XMLConcat()	Takes as input a series of XMLType instances, concatenates the series of elements for each row, and returns the concatenated series.
UpdateXML()	Takes as arguments an XMLType instance and an XPath-value pair, and returns an XMLType instance with the updated value.

XMLElement()

Description

XMLElement takes an element name for identifier, an optional collection of attributes for the element, and arguments that make up the element's content. It returns an instance of type XMLType. XMLElement is similar to SYS_XMLGen except that XMLElement can include attributes in the XML returned, but it does not accept formatting using the XMLFormat object.

The XMLElement function is typically nested to produce an XML document with a nested structure, as in the example in the following section.

You must specify a value for identifier, which Oracle uses as the enclosing tag. The identifier does not have to be a column name or column reference. It cannot be an expression or null.

In the XML_attributes_clause, if the value_expr is null, then no attribute is created for that value expression. The type of value_expr cannot be an object type or collection.

The objects that make up the element content follow the XMLATTRIBUTES keyword.

• If value_expr is a scalar expression, then you can omit the AS clause, and Oracle 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.
• If value_expr is null, then no element is created for that value expression.

Syntax

XMLELEMENT(   elementname IN VARCHAR2,
              [XMLATTRIBUTES(),] 
               value_expr, 
               ...)  
               RETURN XMLType

XMLATTRIBUTES( value_expr [AS identifier],
               ...) 
               RETURN XMLType

Parameter	Description
elementname	Indicates the name of the XML element to be generated
value_expr	This must be a scalar value for XMLATTRIBUTES. It can be a nested XMLELEMENT call for XMLELEMENT.

XMLForest()

Description

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 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.
• If value_expr is null, then no element is created for that value_expr.

Syntax

XMLFOREST( value_expr [AS identifier],
           ...)
           RETURN XMLType

Parameter	Description
value_expr	May be a scalar, object, or nested call.

XMLColAttVal()

Description

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: You cannot specify an object type column for value_expr.

Syntax

XMLCOLATTVAL( value_expr [AS identifier],
              ...)
              RETURN XMLType

Parameter	Description
value_expr	May be a scalar, object, or nested call.

ExtractValue()

Description

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, the element must have a single text node as its child, and it is this value that the function returns. If the specified XPath points to a node with more than one child, or if the node pointed to has a non-text node child, Oracle returns an error.

For documents based on XML schemas, if Oracle can infer the type of the return value, then a scalar value of the appropriate type is returned. Otherwise, the result is of type VARCHAR2. For documents that are not based on XML schemas, the return type is always VARCHAR2.

Syntax

EXTRACTVALUE( instance   IN  XMLType,
              xpath      IN  VARCHAR2, 
             [namespace  IN  VARCHAR2])  
             RETURN value

Parameter	Description
instance	The XMLType to extract the data from.
xpath	XPath expression as a string
namespace	Optional argument specifying namespace declarations.

XMLTransform()

Description

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.

Syntax

XMLTRANSFORM( instance    IN   XMLType,
              xslt        IN   XMLType)
              RETURN XMLType

Parameter	Description
instance	The XMLType instance to be transformed.
xslt	The XSLT document to transform the instance.

XMLSequence()

Description

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.
• 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.

Syntax

XMLSEQUENCE( { input IN XMLType | input IN SYS_REFCURSOR,
               [format IN XMLFormat] })
             RETURN XMLType

Parameter	Description
input	The data to be split up.
format	The optional XMLFormat to be used to format the output from the cursor.

XMLConcat()

Description

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.

The options are described in the following table.

Syntax	Description
XMLCONCAT( data IN XMLSEQUENCETYPE ) RETURN XMLType	Takes in one argument of type XMLSequenceType, and retruns an XMLType which is the concatenation of all XMLTypes in the sequence.
XMLCONCAT ( arg1 IN XMLType, ...) RETURN XMLType	This is an n-ary function that constructs a forest of XML elements from its arguments. If an argument is the NULL value, then it is quietly dropped from the result. If all of the arguments are the NULL value, then the NULL value is returned.

 

Parameter	Description
data	The XMLSequenceType to be concatenated together.
arg1, arg2, ...	The XMLType arguments to be concatenated together.

UpdateXML()

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. The datatypes of the target of XPath_string and the value_expr must match.

If you update an XML element to null, Oracle removes the attributes and children of the element, and the element becomes empty. If you update the text node of an element to null, Oracle removes the text value of the element, and the element itself remains but is empty.

In most cases, this function materializes an XML document in memory and updates the value. However, UPDATEXML is optimized for UPDATE statements on object-relational columns so that the function updates the value directly in the column. This optimization requires the following conditions:

• The XMLType_instance must be the same as the column in the UPDATE ... SET clause.
• The XPath_string must resolve to scalar content.

Syntax

UPDATEXML( instance  IN XMLType,
           [xpath     IN VARCHAR2,
           value_expr)], ...,
           [namespace IN VARCHAR2] )
          RETURN XMLType

Parameter	Description
instance	The XMLType to be updated.
xpath	The location in the XMLType to be changed.
value_expr	The value to update the location to. May be an XMLType or a string, depending on the location pointed to.
namespace	Optional argument specifying namespace declarations.