24
XMLType API for PL/SQL

This chapter describes the types and functions used for native XML support in the server, namely:

• Description of XMLType
• Functions and Procedures of XMLType

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

---

XMLType

---

Description of XMLType

XMLType is a system-defined opaque type for handling XML data. XMLType has predefined member functions on it to extract XML nodes and fragments.

You can create columns of XMLType and insert XML documents into it. You can also generate XML documents as XMLType instances dynamically using the SYS_XMLGEN and SYS_XMLAGG SQL functions.

For example,

CREATE TABLE Xml_tab ( xmlval xmltype);

INSERT INTO Xml_tab VALUES (
   xmltype('<?xml version="1.0"?>
               <EMP>
                  <EMPNO>221</EMPNO>
                  <ENAME>John</ENAME>
               </EMP>'));

INSERT INTO Xml_tab VALUES (
   xmltype('<?xml version="1.0"?>
               <PO>
                  <PONO>331</PONO>
                  <PONAME>PO_1</PONAME>
               </PO>'));

-- now extract the numerical values for the employee numbers

SELECT e.xmlval.extract('//EMPNO/text()').getNumVal() as empno
   FROM Xml_tab 
   WHERE e.xmlval.existsnode('/EMP/EMPNO')  = 1;

---

Functions and Procedures of XMLType

Table 24-1 Summary of Functions and Procedures of XMLType  

Function	Description
XMLType()	Constructor that constructs an instance of the XMLType datatype. The constructor can take in the XML as a CLOB, VARCHAR2 or take in a object type.
createXML()	Static function for creating and returning an XMLType instance.
existsNode()	Takes a XMLType instance and a XPath and returns 1 or 0 indicating if applying the XPath returns a non-empty set of nodes.
extract()	Takes a XMLType instance and an XPath, applies the XPath expression and returns the results as an XMLType.
isFragment()	Checks if the input XMLType instance is a fragment or not. A fragment is a XML instance, which has more than one root element.
getClobVal()	Returns the value of the XMLtype instance as a CLOB
getNumVal()	Returns the value of the XMLtype instance as a NUMBER. This is only valid if the input XMLtpye instance contains a simple text node and is convertible to a number.
getStringVal()	Returns the value of the XMLType instance as a string.
transform()	Takes an XMLtype instance and an associated stylesheet (which is also an XMLtype instance) , applies the stylesheet and returns the result as XML.
toObject()	Converts the XMLType instance to an object type.
isSchemaBased()	Returns 1 or 0 indicating if the input XMLType instance is a schema based one or not.
getSchemaURL()	Returns the XML schema URL if the input is a XMLSchema based.
getRootElement()	Returns the root element of the input instance. Returns NULL if the instance is a fragment
createSchemaBasedXML()	Creates a schema based XMLtype instance from the non-schema based instance using the input schema URL.
createNonSchemaBasedXML()	Creates a non schema based XML from the input schema based instance.
getNamespace()	Returns the namespace for the top level element in a schema based document.
schemaValidate()	Validates the input instance according to the XMLSchema. Raises error if the input instance is non-schema based.
isSchemaValidated()	Checks if the instance has been validated against the schema.
setSchemaValidated()	Sets the schema valid flag to avoid costly schema validation.
isSchemaValid()	Checks if the input instance is schema valid according to the given schema URL.

XMLType()

Description

XMLType constructor. The options are described in the following table.

Syntax	Description
constructor function XMLType( xmlData IN clob, schema IN varchar2 := NULL, validated IN number := 0, wellformed IN Number := 0) return self as result	This constructor function creates an optionally schema-based XMLType instance using the specified schema and xml data prameters.
constructor function XMLType( xmlData IN varchar2, schema IN varchar2 := NULL, validated IN number := 0, wellformed IN number := 0) return self as result	This constructor function creates an optionally schema-based XMLType instance using the specified schema and xml data prameters.
constructor function XMLType ( xmlData IN "<ADT_1>", schema IN varchar2 := NULL, element IN varchar2 := NULL, validated IN number := 0) return self as result	This constructor function creates an optionally schema-based XMLType instance from the specified object type parameter.
onstructor function XMLType( xmlData IN SYS_REFCURSOR, schema in varchar2 := NULL, element in varchar2 := NULL, validated in number := 0) return self as result	This constructor function creates an optionally schema-based XMLType instance from the specified REF CURSOR parameter.

Parameter	IN / OUT	Description
xmlData	(IN)	The actual data in the form of a CLOB, REF cursor, VARCHAR2 or object type.
schema	(IN)	Optional Schema URL to be used to make the input conform to the given schema.
validated	(IN)	Flag to indicate that the instance is valid according to the given XMLSchema. (default 0)
wellformed	(IN)	Flag to indicate that the input is wellformed. If set, then the database would not do well formed check on the input instance. (default 0)
element	(IN)	Optional element name in the case of the ADT_1 or REF CURSOR constructors. (default null)

createXML()

Description

Static function for creating and returning an XMLType instance. The string and clob parameters used to pass in the date must contain well-formed and valid XML documents. The options are described in the following table.

Syntax	Description
STATIC FUNCTION createXML( xmlval IN varchar2) RETURN XMLType deterministic	Creates the XMLType instance from a string.
STATIC FUNCTION createXML( xmlval IN clob) RETURN XMLType	Creates the XMLType instance from a CLOB.
STATIC FUNCTION createXML ( xmlData IN clob, schema IN varchar2, validated IN number := 0, wellformed IN number := 0 ) RETURN XMLType deterministic	This static function creates a schema-based XMLType instance using the specified schema and xml data prameters.
STATIC FUNCTION createXML ( xmlData IN varchar2, schema IN varchar2, validated IN number := 0, wellformed IN number := 0) RETURN XMLType deterministic	This static function creates a schema-based XMLType instance using the specified schema and xml data prameters.
STATIC FUNCTION createXML ( xmlData IN "<ADT_1>", schema IN varchar2 := NULL, element IN varchar2 := NULL, validated IN NUMBER := 0) RETURN XMLType deterministic	Creates an XML instance from an instance of an user-defined type.
STATIC FUNCTION createXML ( xmlData IN SYS_REFCURSOR, schema in varchar2 := NULL, element in varchar2 := NULL, validated in number := 0) RETURN XMLType deterministic	Creates an XML instance from a cursor reference. You can pass in any arbitrary SQL query as a CURSOR.

Parameter	IN / OUT	Description
xmlData	(IN)	The actual data in the form of a CLOB, REF cursor, VARCHAR2 or object type.
schema	(IN)	Optional Schema URL to be used to make the input conform to the given schema.
validated	(IN)	Flag to indicate that the instance is valid according to the given XMLSchema. (default 0)
wellformed	(IN)	Flag to indicate that the input is wellformed. If set, then the database would not do well formed check on the input instance. (default 0)
element	(IN)	Optional element name in the case of the ADT_1 or REF CURSOR constructors. (default null)

existsNode()

Description

Member function. Checks if the node exists. If the XPath string is NULL or the document is empty, then a value of 0 is returned, otherwise returns 1. The options are described in the following table.

Syntax	Description
MEMBER FUNCTION existsNode( xpath IN varchar2) RETURN number deterministic	Given an XPath expression, checks if the XPath applied over the document can return any valid nodes.
MEMBER FUNCTION existsNode( xpath in varchar2, nsmap in varchar2) RETURN number deterministic	This member function uses the XPath expression with the namespace information and checks if applying the XPath returns any nodes or not.

Parameter	IN / OUT	Description
xpath	(IN)	The XPath expression to test.
nsmap	(IN)	Optional namespace mapping.

extract()

Description

Member function. Extracts an XMLType fragment and returns an XMLType instance containing the result node(s). If the XPath does not result in any nodes, then returns NULL. The options are described in the following table.

Syntax	Description
MEMBER FUNCTION extract( xpath IN varchar2) RETURN XMLType deterministic	Given an XPath expression, applies the XPath to the document and returns the fragment as an XMLType.
MEMBER FUNCTION extract( xpath IN varchar2, nsmap IN varchar2) RETURN XMLType deterministic	This member function applies the XPath expression along with the namespace mapping, over the XML data to return a XMLType instance containing the resultant fragment.

Parameter	IN / OUT	Description
xpath	(IN)	The XPath expression to apply.
nsmap	(IN)	Optional prefix to namespace mapping information.

isFragment()

Description

Determines if the XMLType instance corresponds to a well-formed document, or a fragment. Returns 1 or 0 indicating if the XMLType instance contains a fragment or a well-formed document. Returns 1 or 0 indicating if the XMLType instance contains a fragment or a well-formed document.

Syntax

MEMBER FUNCTION isFragment() RETURN number deterministic

getClobVal()

Member function. Returns a CLOB containing the seralized XML representation; if the returns is a temporary CLOB, then it must be freed after use. The options are described in the following table.

Syntax

MEMBER FUNCTION getClobVal() RETURN clob deterministic

getNumVal()

Description

Member function. Returns a numeric value, formatted from the text value pointed to by the XMLType instance. The XMLType must point to a valid text node that contains a numerical value. The options are described in the following table.

Syntax

MEMBER FUNCTION getNumVal() RETURN number deterministic

getStringVal()

Description

Member function. Returns the document as a string. Returns s string containing the seralized XML representation, or in case of text nodes, the text itself. If the XML document is bigger than the maximum size of the varchar2, which is 4000, then an error is raised at run time.

Syntax

MEMBER FUNCTION getStringVal() RETURN varchar2 deterministic

transform()

Description

Member function. This member function transforms the XML data using the XSL stylesheet argument and the top-level parameters passed as a string of name=value pairs. If any of the arguments other than the parammap is NULL, then a NULL is returned.

Syntax

MEMBER FUNCTION transform(xsl IN XMLType, parammap in varchar2 := NULL) RETURN 
XMLType deterministic

Parameter	IN / OUT	Description
xsl	(IN)	The XSL stylesheet describing the transformation
parammap	(IN)	Top level parameters to the XSL - string of name=value pairs

toObject()

Description

Member procedure. This member procedure converts the XML data into an instance of a user defined type, using the optional schema and top-level element arguments.

Syntax

MEMBER PROCEDURE toObject(SELF in XMLType, object OUT "<ADT_1>", schema in 
varchar2 := NULL, element in varchar2 := NULL)

Parameter	IN / OUT	Description
SELF	(IN)	The instance to be converted. This is implicit if you use it as a member procedure.
object	(IN)	the converted object. An object instance of the required type may be passed in to this function
schema	(IN)	the schema URL. The mapping of the XMLType instance to the converted object instance may be specified using a schema.
element	(IN)	.the top-level element name. An XMLSchema document does not specify the top-level element for a conforming XML instance document. The top-level element name in the XMLSchema document to map the XMLType instance is specified with this parameter.

isSchemaBased()

Description

Member function. Determines whether the XMLType instance is schema-based or not. Returns 1 or 0 depending on whether the XMLType instance is schema-based or not.

Syntax

MEMBER FUNCTION isSchemaBased return number deterministic

getSchemaURL()

Description

Member function. Returns the XMLSchema URL corresponding to the XMLType instance, if the XMLType instance is a schema-based document. Otherwise returns NULL.

Syntax

MEMBER FUNCTION getSchemaURL return varchar2 deterministic

getRootElement()

Description

Member function. Gets the root element of the XMLType instance. Returns NULL if the instance is a fragment.

Syntax

MEMBER FUNCTION getRootElement return varchar2 deterministic

createSchemaBasedXML()

Description

Member function. Creates a schema based XMLType instances from a non-schema based XML and a schemaURL.

Syntax

MEMBER FUNCTION createSchemaBasedXML(schema IN varchar2 := NULL) return 
sys.XMLType deterministic

Parameter	IN / OUT	Description
schema	(IN)	the schema URL If this parameter is NULL, then the XMLType instance must contain a schema URL.

createNonSchemaBasedXML()

Description

Member function. Creates a non schema based XML document from a schema based instance.

Syntax

MEMBER FUNCTION createNonSchemaBasedXML return XMLType deterministic 

getNamespace()

Description

Member function. Returns the namespace of the top level element in the instance. Returns NULL if the input is a fragment or is a non-schema based instance.

Syntax

MEMBER FUNCTION getNamespace return varchar2 deterministic

schemaValidate()

Description

Member procedure. Validates the XML instance against its schema if it hasn't already been done so. For non-schema based documents an error is raised. If validation fails an error is raised; else, the document's status is changed to validated.

Syntax

MEMBER PROCEDURE schemaValidate

isSchemaValidated()

Description

Member function. Returns the validation status of the XMLType instance -- tells if a schema based instance has been actually validated against its schema. Returns 1 if the instance has been validated against the schema, 0 otherwise.

Syntax

MEMBER FUNCTION isSchemaValidated return NUMBER deterministic

setSchemaValidated()

Description

Memebr function. Sets the VALIDATION state of the input XML instance.

Syntax

MEMBER PROCEDURE setSchemaValidated(flag IN BINARY_INTEGER := 1)

Parameter	IN / OUT	Description
flag	(IN)	0 - NOT VALIDATED; 1 - VALIDATED; The default value for this parameter is 1.

isSchemaValid()

Description

Memebr function. Checks if the input instance is conformant to a specified schema. Does not change the validation status of the XML instance. If a XML Schema URL is not specified and the xml document is schema based, the conformance is checked against the XMLType instance's own schema.

Syntax

member function isSchemaValid(schurl IN VARCHAR2 := NULL, elem IN VARCHAR2 := 
NULL) return NUMBER deterministic

Parameter	IN / OUT	Description
schurl	(IN)	The URL of the XML Schema against which to check conformance.
elem	(IN)	Element of a specified schema, against which to validate. This is useful when we have a XML Schema which defines more than one top level element, and we want to check conformance against a specific one of these elements.