Oracle XMLTYPE Demos

Oracle XMLTYPE Version 11.2.0.3

General Information

Purpose

AUTHID CURRENT_USER

Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'XMLTYPE'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'XMLTYPE';

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

APPENDCHILDXML	GETCLOBVAL	ISSCHEMABASED
CREATENONSCHEMABASEDXML	GETNAMESPACE	ISSCHEMAVALID
CREATESCHEMABASEDXML	GETNUMBERVAL	ISSCHEMAVALIDATED
CREATEXML	GETROOTELEMENT	SCHEMAVALIDATE
CREATEXMLFROMBINARY	GETSCHEMAID	SETSCHEMAVALIDATED
DELETEXML	GETSCHEMAURL	TOOBJECT
EXISTSNODE	GETSTRINGVAL	TRANSFORM
EXTRACT	INSERTXMLBEFORE	XMLTYPE
GETBLOBVAL	ISFRAGMENT

Demo Table and Data 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 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 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

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