Oracle XMLTYPE Data Type
Version 12.1.0.1

General Information
Library Note [an error occurred while processing this directive]
Purpose A data type that understands native XML and contains methods for validating, transforming, and modifying XML documents.
AUTHID CURRENT_USER
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'XMLTYPE'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'XMLTYPE';

Returns 366 objects
Documented No
First Available 9.0.1.1.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC with GRANT OPTION
Source {ORACLE_HOME}/rdbms/admin/dbmsxmlt.plb
Subprograms
 
Demo Tables and Data
Build Script CREATE TABLE xmltypetab OF XMLTYPE;

CREATE TABLE shopping_cart (
cart_id   NUMBER,
cart_data XMLTYPE,
upd_date  DATE);

INSERT INTO shopping_cart
(cart_id, cart_data, upd_date)
VALUES
(1, '<?xml version="1.0" encoding="utf-8"?>
       <ShoppingCartData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <Id>Morgan</Id>
         <SiteId>1</SiteId>
         <ProductId>100</ProductId>
         <Quantity>2</Quantity>
       </ShoppingCartData>',
SYSDATE);

COMMIT;
 
APPENDCHILDXML
Undocumented MEMBER FUNCTION appendChildXML(
xpath      IN VARCHAR2,
value_expr IN XMLType,
namespace  IN VARCHAR2 := NULL)
RETURN XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
 
CREATENONSCHEMABASEDXML
Creates a non-schema based XML from the input schema based instance (self) MEMBER FUNCTION createNonSchemaBasedXML RETURN sys.XMLType
DETERMINISTIC PARALLEL_ENABLE;
SELECT sc.cart_data.createNonSchemaBasedXML()
FROM shopping_cart sc;
 
CREATESCHEMABASEDXML
Creates a schema based XMLType instance from the non-schema based instance using the input schema URL MEMBER FUNCTION createSchemaBasedXML(schema IN VARCHAR2 := NULL)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
SELECT sc.cart_data.createSchemaBasedXML()
FROM shopping_cart sc;
 
CREATEXML
Static function for creating and returning an XMLType instance

Overload 1
STATIC FUNCTION createXML(xmlData IN CLOB)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
DECLARE
 c CLOB := '<?xml version="1.0" encoding="utf-8"?>
       <ShoppingCartData xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <Id>ABCDEFG</Id>
         <SiteId>1</SiteId>
         <ProductId>100</ProductId>
         <Quantity>2</Quantity>
       </ShoppingCartData>';
 x XMLType;
BEGIN
  x := xmlType.createXML(c);
END;
/
Overload 2 STATIC FUNCTION createXML(xmlData IN VARCHAR2)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
DECLARE
 v VARCHAR2(512) := '<?xml version="1.0" encoding="utf-8"?>
       <ShoppingCartData xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <Id>ABCDEFG</Id>
         <SiteId>1</SiteId>
         <ProductId>100</ProductId>
         <Quantity>2</Quantity>
       </ShoppingCartData>';
 x XMLType;
BEGIN
  x := xmlType.createXML(v);
END;
/
Overload 3 STATIC FUNCTION createXML (
xmlData    IN CLOB,
schema     IN VARCHAR2,
validated  IN NUMBER := 0,
wellformed IN NUMBER := 0)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 4 STATIC FUNCTION createXML(
xmlData    IN BLOB,
csid       IN NUMBER,
schema     IN VARCHAR2,
validated  IN NUMBER := 0,
wellformed IN NUMBER := 0)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 5 STATIC FUNCTION createXML(
xmlData    IN BFILE,
csid       IN NUMBER,
schema     IN VARCHAR2,
validated  IN NUMBER := 0,
wellformed IN NUMBER := 0)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 6 STATIC FUNCTION createXML(
xmlData    IN VARCHAR2,
schema     IN VARCHAR2,
validated  IN NUMBER := 0,
wellformed IN NUMBER := 0)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 7 STATIC FUNCTION createXML(
xmlData   IN "<ADT_1>",
schema    IN VARCHAR2 := NULL,
element   IN VARCHAR2 := NULL,
validated IN NUMBER := 0)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 8 STATIC FUNCTION createXML(
xmlData   IN SYS_REFCURSOR,
schema    IN VARCHAR2 := NULL,
element   IN VARCHAR2 := NULL,
validated IN NUMBER := 0)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 9 STATIC FUNCTION createXML(
xmlData   IN AnyData,
schema    IN VARCHAR2 := NULL,
element   IN VARCHAR2 := NULL,
validated IN NUMBER := 0)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
 
CREATEXMLFROMBINARY
Undocumented STATIC FUNCTION createXMLFromBinary(xmlData IN BLOB)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
 
DELETEXML
Undocumented MEMBER FUNCTION deleteXML(
xpath     IN VARCHAR2,
namespace IN VARCHAR2 := NULL)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
 
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

Overload 1
MEMBER FUNCTION existsNode(xpath IN VARCHAR2) RETURN NUMBER
DETERMINISTIC PARALLEL_ENABLE;
SELECT COUNT(*)
FROM xmltypetab
WHERE existsNode(object_value,'/ShoppingCartData/SiteId') = 1;

INSERT INTO xmltypetab
VALUES
('<?xml version="1.0" encoding="utf-8"?>
       <ShoppingCartData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <Id>Amazon.com</Id>
         <SiteId>1</SiteId>
         <ProductId>100</ProductId>
         <Quantity>2</Quantity>
       </ShoppingCartData>');

COMMIT;

SELECT COUNT(*)
FROM xmltypetab
WHERE existsNode(object_value,'/ShoppingCartData/SiteId') = 2;

SELECT COUNT(*)
FROM xmltypetab
WHERE existsNode(object_value,'/ShoppingCartData/SiteId') = 1;
Overload 2 MEMBER FUNCTION existsNode(xpath in VARCHAR2, nsmap in VARCHAR2)
RETURN NUMBER DETERMINISTIC PARALLEL_ENABLE;
TBD
 
EXTRACT
Takes a XMLType instance and an XPath and applies the XPath expression

Overload 1
MEMBER FUNCTION extract(xpath IN VARCHAR2) RETURN sys.XMLType
DETERMINISTIC PARALLEL_ENABLE;
CREATE TABLE xmldemo OF XMLType;
create table some_things of xmltype;

INSERT INTO xmldemo VALUES
(XMLType('<things>
    <numbers>
      <item>1</item>
      <item>59</item>
    </numbers>
    <animals>
      <item>bird</item>
      <item>cat</item>
      <item>dog</item>
    </animals>
          </things>'));

INSERT INTO xmldemo VALUES
(XMLType('<things>
    <countries>
      <item>Canada</item>
      <item>Russia</item>
       <item>Finland</item>
    </countries>
    <numbers>
      <item>42</item>
      <item>2012</item>
    </numbers>
          </things>'));

INSERT INTO xmldemo VALUES
(XMLType('<things>
    <cities>
      <item>Vancouver</item>
      <item>St. Petersburg</item>
      <item>Helsinki</item>
    </cities>
    <animals>
      <item>mongoose</item>
      <item>grey goose</item>
    </animals>
          </things>'));

SELECT extract(object_value,'/things/animals/item[position()=2]') "2nd Animal"
FROM xmldemo;
Overload 2 MEMBER FUNCTION extract(xpath IN VARCHAR2, nsmap IN VARCHAR2)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
 
GETBLOBVAL
Extracts XMLTYPE and casts it as a BLOB
Overload 1
MEMBER FUNCTION getBlobVal(csid IN NUMBER) RETURN BLOB
DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 2 MEMBER FUNCTION getBlobVal(
csid   IN NUMBER,
pflag  IN NUMBER,
indent IN NUMBER)
RETURN BLOB DETERMINISTIC PARALLEL_ENABLE;
TBD
 
GETCLOBVAL
Extracts XMLTYPE and casts it as a CLOB

Overload 1
MEMBER FUNCTION getClobVal RETURN CLOB DETERMINISTIC PARALLEL_ENABLE;
CREATE TABLE cart_with_clob AS
SELECT sc.cart_id, sc.cart_data.getCLOBVal() AS CD_CLOB, sc.upd_date
FROM shopping_cart sc;
Overload 2 MEMBER FUNCTION xmltype.getCLOBVal(
self   IN XMLTYPE,
pflag  IN NUMBER,
indent IN NUMBER)
RETURN CLOB DETERMINISTIC PARALLEL_ENABLE;
TBD
 
GETNAMESPACE
Returns the namespace for the top level element in a schema based document MEMBER FUNCTION getNamespace RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE;
DECLARE
 x XMLType;
BEGIN
  SELECT cart_data
  INTO x
  FROM shopping_cart
  WHERE rownum = 1;

  dbms_output.put_line(x.getNamespace);
END;
/
 
GETNUMBERVAL
Returns the value of the XMLType instance as a NUMBER. This is only valid if the input XMLType instance contains a simple text node and is convertible to a number MEMBER FUNCTION getNumberVal RETURN NUMBER DETERMINISTIC PARALLEL_ENABLE;
TBD
 
GETROOTELEMENT
Returns the root element of the input instance. Returns NULL if the instance is a fragment MEMBER FUNCTION xmltype.getRootElement RETURN VARCHAR2
DETERMINISTIC PARALLEL_ENABLE;
SELECT sc.cart_data.getRootElement()
FROM shopping_cart sc;
 
GETSCHEMAID
Undocumented MEMBER FUNCTION  xmltype.getSchemaID RETURN RAW DETERMINISTIC PARALLEL_ENABLE;
TBD
 
GETSCHEMAURL
Returns the XML schema URL if the input XML Schema based MEMBER FUNCTION xmltype.getSchemaURL RETURN VARCHAR2
DETERMINISTIC PARALLEL_ENABLE;
TBD
 
GETSTRINGVAL
Returns the value of the XMLType instance as a string

Overload 1
MEMBER FUNCTION xmltype.getStringVal() RETURN VARCHAR2
DETERMINISTIC PARALLEL_ENABLE;
SELECT sc.cart_data.getStringVal()
FROM shopping_cart sc;
Overload 2 MEMBER FUNCTION getStringVal(pflag IN NUMBER, indent IN NUMBER)
RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE;
TBD
 
INSERTXMLBEFORE
Undocumented MEMBER FUNCTION insertXMLBefore(
xpath      IN VARCHAR2,
value_expr IN XMLType,
namespace  IN VARCHAR2 := NULL)
RETURN XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
 
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 MEMBER FUNCTION xmltype.isFragment RETURN NUMBER DETERMINISTIC PARALLEL_ENABLE;
SELECT sc.cart_data.isFragment()
FROM shopping_cart sc;
 
ISSCHEMABASED
Returns 1 or 0 indicating if the input XMLType instance is a schema based one or not MEMBER FUNCTION isSchemaBased RETURN NUMBER
DETERMINISTIC PARALLEL_ENABLE;
SELECT sc.cart_data.isSchemaBased()
FROM shopping_cart sc;
 
ISSCHEMAVALID
Checks if doc conforms to a specified schema: non mutating MEMBER FUNCTION isSchemaValid(
schurl IN VARCHAR2 := NULL,
elem   IN VARCHAR2 := NULL)
RETURN NUMBER DETERMINISTIC PARALLEL_ENABLE;
conn uwclass/uwclass@pdbdev

set serveroutput on

DECLARE
 x XMLType;
BEGIN
  SELECT cart_data
  INTO x
  FROM shopping_cart
  WHERE rownum = 1;

  FOR i IN 1.. 2 LOOP
    IF x.isSchemaValid = 1 THEN
      dbms_output.put_line('Valid');
    ELSE
      dbms_output.put_line('Invalid');
    END IF;

    IF x.isSchemaValidated = 1 THEN
      dbms_output.put_line('Validated');
    ELSE
      dbms_output.put_line('Not Validated');
      x.setSchemaValidated;
    END IF;
  END LOOP;
END;
/
 
ISSCHEMAVALIDATED
Returns the value of the VALIDATED flag of the document; tells if a schema based doc. has been actually validated against its schema MEMBER FUNCTION isSchemaValidated RETURN NUMBER DETERMINISTIC PARALLEL_ENABLE;
See isSchemaValid Demo Above
 
SCHEMAVALIDATE
Validates the input instance according to the XML Schema. Raises an error if the input instance is non-schema based MEMBER PROCEDURE schemaValidate(self IN OUT NOCOPY XMLType);
TBD
 
SETSCHEMAVALIDATED
Sets the schema valid flag to avoid costly schema validation MEMBER PROCEDURE setSchemaValidated(
self IN OUT NOCOPY XMLType,
flag IN            BINARY_INTEGER := 1);
See isSchemaValid Demo Above
 
TOOBJECT
Converts the XMLType instance to an object type MEMBER PROCEDURE toObject(
self    IN  sys.XMLType,
object  OUT "<ADT_1>",
schema  IN  VARCHAR2 := NULL,
element IN  VARCHAR2 := NULL);
TBD
 
TRANSFORM
Takes an XMLType instance and an associated stylesheet (which is also an XMLType instance), applies the stylesheet and returns the result as XML MEMBER FUNCTION transform(
xsl      IN sys.XMLType,
parammap IN VARCHAR2 := NULL)
RETURN sys.XMLType DETERMINISTIC PARALLEL_ENABLE;
TBD
 
XMLTYPE
Constructs an instance of the XMLType datatype. The constructor can take in the XML as a CLOB, VARCHAR2 or an object type

Overload 1
CONSTRUCTOR FUNCTION XMLType(
xmlData    IN CLOB,
schema     IN VARCHAR2 := NULL,
validated  IN NUMBER := 0,
wellformed IN NUMBER := 0)
RETURN SELF AS RESULT DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 2 CONSTRUCTOR FUNCTION XMLType(
xmlData    IN BLOB,
csid       IN NUMBER,
schema     IN VARCHAR2 := NULL,
validated  IN NUMBER := 0,
wellformed IN NUMBER := 0)
RETURN SELF AS RESULT DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 3 CONSTRUCTOR FUNCTION XMLType(
xmlData    IN BFILE,
csid       IN NUMBER,
schema     IN VARCHAR2 := NULL,
validated  IN NUMBER := 0,
wellformed IN NUMBER := 0)
RETURN SELF AS RESULT DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 4 CONSTRUCTOR FUNCTION XMLType(
xmlData    IN VARCHAR2,
schema     IN VARCHAR2 := NULL,
validated  IN NUMBER := 0,
wellformed IN NUMBER := 0)
RETURN SELF AS RESULT DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 5 CONSTRUCTOR FUNCTION XMLType(
xmlData   IN "<ADT_1>",
schema    IN VARCHAR2 := NULL,
element   IN VARCHAR2 := NULL,
validated IN NUMBER := 0)
RETURN SELF AS RESULT DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 6 CONSTRUCTOR FUNCTION XMLType(
xmlData   IN AnyData,
schema    IN VARCHAR2 := NULL,
element   IN VARCHAR2 := NULL,
validated IN NUMBER := 0)
RETURN SELF AS RESULT DETERMINISTIC PARALLEL_ENABLE;
TBD
Overload 7 CONSTRUCTOR FUNCTION XMLType(
xmlData   IN SYS_REFCURSOR,
schema    IN VARCHAR2 := NULL,
element   IN VARCHAR2 := NULL,
validated IN NUMBER := 0)
RETURN SELF AS RESULT DETERMINISTIC PARALLEL_ENABLE;
TBD

Related Topics
Data Types
DBMS_XMLGEN
DBMS_XMLPARSER
DBMS_XMLSAVE
DBMS_XMLSCHEMA
DBMS_XMLSTORE
DBMS_XMLTRANSLATIONS
XML Functions
XMLQuery
XMLTable
XMLTables

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved