Oracle XML Functions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Note: Run these demos top to bottom without using COMMIT or ROLLBACK.
 
APPENDCHILDXML
Appends a user-supplied value onto the target XML as the child of the node indicated by an XPath expression APPENDCHILDXML(XMLType_Instance>, <XPath_string>, <value_expression>, <namespace_string>)
conn oe/oe@pdborcl

UPDATE warehouses
SET warehouse_spec = APPENDCHILDXML(warehouse_spec,
'Warehouse/Building', XMLType('<Owner>Grandco</Owner>'))
WHERE EXTRACTVALUE(warehouse_spec, '/Warehouse/Building') = 'Rented';

col "Prop.Owner" format a30

SELECT warehouse_id, warehouse_name,
EXTRACTVALUE(warehouse_spec, '/Warehouse/Building/Owner') "Prop.Owner"
FROM warehouses
WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Building/Owner') = 1;
-- based on the shopping_cart data and table on the Undocumented Oracle page of the library

SELECT * FROM shopping_cart;

UPDATE shopping_cart
SET cart_data =
    appendChildXML(cart_data, 'ShoppingCartData/Id', XMLType('<Owner>Morgan</Owner>'))
WHERE EXTRACTVALUE(cart_data, '/ShoppingCartData/SiteId') = '1';

SELECT * FROM shopping_cart;
 
DELETEXML
Deletes the node or nodes matched by the XPath expression in the target XML DELETEXML(XMLType_Instance>, <XPath_string>, <namespace_string>)
--must follow APPENDCHILDXML demo (above)

SELECT warehouse_id, warehouse_spec
FROM warehouses
WHERE warehouse_id IN (2,3);

UPDATE warehouses
SET warehouse_spec=DELETEXML(warehouse_spec,'/Warehouse/Building/Owner')
WHERE warehouse_id = 2;

col warehouse_spec format a60

SELECT warehouse_id, warehouse_spec
FROM warehouses
WHERE warehouse_id IN (2,3);
 
DEPTH
Returns the number of levels in the path specified by the UNDER_PATH condition with the same correlation variable DEPTH(correlation_integer) RETURN NUMBER;
conn oe/oe@pdborcl

SELECT PATH(1), DEPTH(2)
FROM RESOURCE_VIEW
WHERE UNDER_PATH(res, '/sys/schemas/OE', 1)=1
AND UNDER_PATH(res, '/sys/schemas/OE', 2)=1;
 
EXISTSNODE
Determines whether traversal of an XML document using a specified path results in any nodes EXISTSNODE(XMLType_Instance>, <XPath_string>, <namespace_string>) RETURN BOOLEAN
conn oe/oe@pdborcl

SELECT warehouse_id, warehouse_name
FROM warehouses
WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Docks') = 1;
 
EXTRACT
Applies a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment EXTRACT(XMLType_Instance>, <XPath_string>, <namespace_string>)
conn oe/oe@pdborcl

col "Number of Docks" format a30

SELECT warehouse_name,
EXTRACT
(warehouse_spec, '/Warehouse/Docks') "Number of Docks"
FROM warehouses
WHERE warehouse_spec IS NOT NULL;
Demo using the capability in a non-XML environment conn uwclass/uwclass@pdbdev

rollback;

conn uwclass/uwclass

CREATE TABLE t(
line  NUMBER(3),
site  VARCHAR2(4),
phase VARCHAR2(5),
test  VARCHAR2(25));

INSERT INTO t VALUES (1, '0100', '*','1111111111111111111111111');
INSERT INTO t VALUES (2, '0100', '=','2222222222222222222222222');
INSERT INTO t VALUES (3, '0100', '=','3333333333333333333333333');
INSERT INTO t VALUES (4, '0100', '*','4444444444444444444444444');
INSERT INTO t VALUES (5, '0100', '=','5555555555555555555555555');
INSERT INTO t VALUES (6, '0200', '*','6666666666666666666666666');

SELECT * FROM t;

col text format a75

SELECT group_key AS "Line#", site, EXTRACT(XMLAGG(xmlelement("V", test)), '/V/text()') AS text
FROM (
  SELECT line, site, SUM(to_number(TRANSLATE(phase, '*=', '10')))
  OVER (ORDER BY line) AS group_key, test
  FROM t)
GROUP BY site, group_key;
 
EXTRACTVALUE
Takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node EXTRACTVALUE(XMLType_Instance>, <XPath_string>, <namespace_string>)
conn oe/oe@pdborcl

col docks format a30

SELECT warehouse_name,
EXTRACTVALUE(e.warehouse_spec, '/Warehouse/Docks') "Docks"
FROM warehouses e
WHERE warehouse_spec IS NOT NULL;
 
INSERTCHILDXML
Inserts a user-supplied value into the target XML at the node indicated by the XPath expression INSERTCHILDXML(XMLType_Instance>, <XPath_string>,
<child_expression>, <value_expression>, <namespace_string>)
conn oe/oe@pdborcl

set long 1000000

SELECT warehouse_spec
FROM warehouses
WHERE warehouse_id = 3;

UPDATE warehouses
SET warehouse_spec = INSERTCHILDXML(warehouse_spec,
'/Warehouse/Building', 'Owner', XMLType('<Owner>LesserCo</Owner>'))
WHERE warehouse_id = 3;

SELECT warehouse_spec
FROM warehouses
WHERE warehouse_id = 3;
 
INSERTCHILDXMLAFTER (new 12.1)
Inserts one or more collection elements as childre<XMLType_instance>, <XPath_string>, <child_expression>, <value_expression> [, <namespace_string>]);n of target parent elements. The insertion for each target occurs immediately after a specified existing collection element. The existing XML document that is the target of the insertion can be schema- or non-schema-based. INSERTCHILDXMLAFTER(<XMLType_instance>, <XPath_string>, <child_expression>, <value_expression> [, <namespace_string>]);
conn oe/oe@pdborcl

UPDATE warehouses
SET warehouse_spec = INSERTCHILDXMLAFTER(warehouse_spec, '/Warehouse/Building',
'Owner[2]', XMLType('<Owner>ThirdOwner</Owner>'))
WHERE warehouse_id = 3;

SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Building/Owner') "Owners"
FROM warehouses
WHERE warehouse_id = 3;
 
INSERTCHILDXMLBEFORE (new 12.1)
Inserts one or more collection elements as children of target parent elements. The insertion for each target occurs immediately before a specified existing collection element. The existing XML document that is the target of the
insertion can be schema- or non-schema-based.
INSERTCHILDXMLBEFORE(<XMLType_instance>, <XPath_string>, <child_expression>, <value_expression> [, <namespace_string>]);
conn oe/oe@pdborcl

UPDATE warehouses
SET warehouse_spec = INSERTCHILDXMLBEFORE(warehouse_spec, '/Warehouse/Building',
'Owner[2]', XMLType('<Owner>ThirdOwner</Owner>'))
WHERE warehouse_id = 3;

SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Building/Owner') "Owners"
FROM warehouses
WHERE warehouse_id = 3;
 
INSERTXMLAFTER
Inserts a user-supplied value into the target XML after the node indicated by the XPath expression INSERTXMLBEFORE(XMLType_Instance>, <XPath_string>, <value_expression>, <namespace_string>)
conn oe/oe@pdborcl

SELECT warehouse_spec
FROM warehouses
WHERE warehouse_id = 3;

UPDATE warehouses
SET warehouse_spec = INSERTXMLAFTER(warehouse_spec,
'/Warehouse/Building',
XMLType('<Owner>Morgan</Owner>'))
WHERE warehouse_id = 3;

SELECT warehouse_spec
FROM warehouses
WHERE warehouse_id = 3;
 
INSERTXMLBEFORE
Inserts a user-supplied value into the target XML before the node indicated by the XPath expression INSERTXMLBEFORE(XMLType_Instance>, <XPath_string>, <value_expression>, <namespace_string>)
conn oe/oe@pdborcl

SELECT warehouse_spec
FROM warehouses
WHERE warehouse_id = 2;

UPDATE warehouses
SET warehouse_spec = INSERTXMLBEFORE(warehouse_spec,
'/Warehouse/Building',
XMLType('<Owner>Morgan</Owner>'))
WHERE warehouse_id = 2;

SELECT warehouse_spec
FROM warehouses
WHERE warehouse_id = 2;
 
PATH
Returns the relative path that leads to the resource specified in the parent condition PATH(<correlation_integer>) RETURN VARCHAR2;
See CONNECT_BY page
 
SYS_DBURIGEN
Generates a URL of datatype DBURIType to a particular column or row object SYS_DBURIGEN(<column_or_attribute> [rowid] [,'text()']);
conn oe/oe@pdborcl

SELECT SYS_DBURIGEN(employee_id, email)
FROM employees
WHERE employee_id = 206;
 
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 SYS_XMLAGG(<expression>, <format>)
conn oe/oe@pdborcl

SELECT SYS_XMLAGG(SYS_XMLGEN(last_name))
FROM employees
WHERE last_name LIKE 'R%';
 
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 SYS_XMLGEN(<expression>, <format>) RETURN XMLType
conn oe/oe@pdborcl

SELECT SYS_XMLGEN(email)
FROM employees
WHERE employee_id = 205;
 
UPDATEXML
Takes as arguments an XMLType instance and an XPath-value pair and returns an XMLType instance with the updated value UPDATEXML(XMLType_Instance>, <XPath_string>, <value_expression>, <namespace_string>) RETYURN XMLType;
conn oe/oe@pdborcl

SELECT warehouse_name,
EXTRACT(warehouse_spec, '/Warehouse/Docks') "Number of Docks"
FROM warehouses
WHERE warehouse_name = 'San Francisco';

UPDATE warehouses
SET warehouse_spec = UPDATEXML(warehouse_spec,
'/Warehouse/Docks/text()',4)
WHERE warehouse_name = 'San Francisco';

SELECT warehouse_name,
EXTRACT(warehouse_spec, '/Warehouse/Docks') "Number of Docks"
FROM warehouses
WHERE warehouse_name = 'San Francisco';
 
XMLAGG
Takes a collection of XML fragments and returns an aggregated XML document. Any arguments that return null are dropped from the result XMLAGG(XMLType_Instance> <ORDER BY CLAUSE>)
conn oe/oe@pdborcl

set long 100000

SELECT XMLELEMENT("Department", XMLAGG(XMLELEMENT("Employee",
e.job_id||' '||e.last_name) ORDER BY last_name)) AS "Dept_list"
FROM employees e
WHERE e.department_id = 30;
 
XMLCAST
XMLCast casts value_expression to the scalar SQL datatype specified by datatype XMLCAST(<value_expression> AS <data_type>)
TBD
 
XMLCDATA
Generates a CDATA section by evaluating value_expr XMLCDATA(<value_expression>)
conn oe/oe@pdborcl

SELECT XMLELEMENT("PurchaseOrder",
XMLAttributes(dummy AS "pono"),
XMLCdata('<!DOCTYPE po_dom_group [
<!ELEMENT po_dom_group(student_name)*>
<!ELEMENT po_purch_name (#PCDATA)>
<!ATTLIST po_name po_no ID #REQUIRED>
<!ATTLIST po_name trust_1 IDREF #IMPLIED>
<!ATTLIST po_name trust_2 IDREF #IMPLIED>
]>')) "XMLCData"
FROM dual;
 
XMLCOLATTVAL
Creates an XML fragment and then expands the resulting XML so that each XML fragment has the name column with the attribute name XMLCOLATTVAL(<value_expression> AS c_alias)
conn oe/oe@pdborcl

SELECT XMLELEMENT("Emp",
XMLCOLATTVAL(e.employee_id, e.last_name, e.salary)) "Emp Element"
FROM employees e
WHERE employee_id = 204;
 
XMLCOMMENT
Generates an XML comment using an evaluated result of value_expr XMLCOMMENT(<value_expression>)
conn oe/oe@pdborcl

SELECT XMLCOMMENT('OrderAnalysisComp imported, reconfigured, disassembled') AS "XMLCOMMENT"
FROM dual;
 
XMLCONCAT
Takes as input a series of XMLType instances, concatenates the series of elements for each row, and returns the concatenated series XMLCONCAT(<XMLType_instance>)
conn oe/oe@pdborcl

SELECT XMLCONCAT(XMLELEMENT("First", e.first_name),
XMLELEMENT("Last", e.last_name)) AS "Result"
FROM employees e
WHERE e.employee_id > 202;
 
XMLDIFF
The XMLDiff function is the SQL interface for the XmlDiff C API XMLDIFF(XMLType_document, XMLType_document, <integer>, <string>)
SELECT XMLDIFF(
XMLTYPE('<?xml version="1.0"?>
<bk:book xmlns:bk="http://nosuchsite.com">
  <bk:tr>
    <bk:td>
      <bk:chapter>
        Chapter 1.
      </bk:chapter>
    </bk:td>
    <bk:td>
      <bk:chapter>
         Chapter 2.
       </bk:chapter>
    </bk:td>
  </bk:tr>
</bk:book>'),
XMLTYPE('<?xml version="1.0"?>
<bk:book xmlns:bk="http://nosuchsite.com">
  <bk:tr>
    <bk:td>
      <bk:chapter>
        Chapter 1.
      </bk:chapter>
    </bk:td>
    <bk:td/>
  </bk:tr>
</bk:book>'))
FROM dual;
 
XMLELEMENT
Takes an element name for identifier or evaluates an element name for EVALNAME value_expr, an optional collection of attributes for the element, and arguments that make up the content of the element XMLELEMENT(<table_columns>)
conn hr/hr@pdborcl

SELECT XMLELEMENT("Emp", XMLELEMENT("Name", e.job_id||' '||e.last_name),
XMLELEMENT("Hiredate", e.hire_date)) AS "Result"
FROM employees e
WHERE employee_id > 200;
conn uwclass/uwclass@pdbdev

CREATE TABLE attraction (
county_name      VARCHAR2(20),
attraction_name  VARCHAR2(30),
attraction_url   VARCHAR2(40),
government_owned CHAR(1),
location         VARCHAR2(20));

INSERT INTO attraction VALUES
('Orange', 'Disneyland', 'http://disney.com', 'N', 'California');
INSERT INTO attraction VALUES
('Merced', 'Yosemite', 'http://usnps.gov', 'Y', 'California');
INSERT INTO attraction VALUES
('Los Angeles', 'Marineland', 'http://marineland.com', 'N', 'California');

SELECT XMLELEMENT("Attraction", XMLAttributes(government_owned AS GOV),
XMLForest(attraction_name AS "Name", location AS "Location", attraction_url AS "URL")) XML_OUTPUT
FROM attraction
WHERE location = 'California';
 
XMLEXISTS
Checks whether a given XQuery expression returns a nonempty XQuery sequence. If so, the function returns TRUE; otherwise, it returns FALSE. XMLEXISTS(XQuery_string PASSING [BY VALUE] expression [AS IDENTIFIER])
TBD
 
XMLFOREST
Converts each of its argument parameters to XML, and then returns an XML fragment that is the concatenation of these converted arguments XMLFOREST(<value_expression> AS c_alias) RETURN XMLType;
conn oe/oe@pdborcl

SELECT XMLELEMENT("Emp", XMLFOREST(e.employee_id, e.last_name, e.salary)) "Emp Element"
FROM employees e
WHERE employee_id = 204;
 
XMLISVALID
Checks if the input instance conforms to a specified XML schema. It does not change the validation status of the XML instance. If any argument is NULL, the result is NULL. If validation fails returns 0 XMLIsValid(XMLType_inst [, schemaurl [, elem]]) RETURN BINARY_INTEGER;
CREATE TABLE po_tab OF XMLTYPE (CHECK (XMLIsValid(object_value) = 1))
XMLSchema "http://www.example.com/schemas/ipo.xsd" ELEMENT "purchaseOrder";
 
XMLPARSE
Parses and generates an XML instance from the evaluated result of value_expression XMLPARSE(<document | content> <value_expression> [WELLFORMED])
conn oe/oe@pdborcl

SELECT XMLPARSE(CONTENT '124 <purchaseOrder poNo="12435">
<customerName> Acme Enterprises</customerName>
<itemNo>32987457</itemNo> </purchaseOrder>' WELLFORMED) AS PO
FROM dual;
 
XMLPATCH
The XMLPatch function is the SQL interface for the XmlPatch C API. This function patches an XML document with the changes specified. The patched XMLType document is returned. XMLPATCH(XMLType_document, XMLType_document)
SELECT XMLPATCH(
XMLTYPE('<?xml version="1.0"?>
<bk:book xmlns:bk="http://nosuchsite.com">
  <bk:tr>
      <bk:td>
        <bk:chapter>
          Chapter 1.
        </bk:chapter>
      </bk:td>
      <bk:td>
        <bk:chapter>
          Chapter 2.
        </bk:chapter>
      </bk:td>
  </bk:tr>
</bk:book>')
,
XMLTYPE('<?xml version="1.0"?>
<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd
  http://xmlns.oracle.com/xdb/xdiff.xsd"
  xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:bk="http://nosuchsite.com">
  <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot"
    diff-algorithm="global"?>
  <xd:delete-node xd:node-type="element"
    xd:xpath="/bk:book[1]/bk:tr[1]/bk:td[2]/bk:chapter[1]"/>
</xd:xdiff>')
)
FROM dual;
 
XMLPI
Generates an XML processing instruction using identifier and optionally the evaluated result of value_expression XMLPI(<name> <identifier>, <value_expression>)
conn oe/oe@pdborcl

SELECT XMLPI(NAME "Order analysisComp", 'imported, reconfigured, disassembled') AS "XMLPI"
FROM dual;
 
XMLQUERY
See XMLQuery Web Page
 
XMLROOT
Create a new XML value by providing version and standalone properties in the XML root information (prolog) of an existing XML value XMLROOT(<value_expression>, VERSION <value_expression | NO VALUE>,
[STANDALONE <YES | NO | NO VALUE>])
conn oe/oe@pdborcl

SELECT XMLROOT(XMLType('<poid>143598</poid>'), VERSION '1.0', STANDALONE YES) AS "XMLROOT"
FROM dual;
 
XMLSEQUENCE
Takes as input an XMLType instance and returns a varray of the top-level nodes in the XMLType

Note: This function exists for backward compatibility but has been deprecated in favor of the XMLTABLE function.
XMLSEQUENCE(<XMLType_Instance>)
conn oe/oe@pdborcl

SELECT EXTRACT(warehouse_spec, '/Warehouse') AS "Warehouse"
FROM warehouses
WHERE warehouse_name = 'San Francisco';

SELECT VALUE(p)
FROM warehouses w,
TABLE(XMLSEQUENCE(EXTRACT(warehouse_spec, '/Warehouse/*'))) p
WHERE w.warehouse_name = 'San Francisco';
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 XMLSEQUENCE(<sys_refcursor_instance IN REF CURSOR, format>)
TBD
 
XMLSERIALIZE
Creates a string or LOB containing the contents of value_expression XMLSERIALIZE(<DOCUMENT | CONTENT> <value_expression> AS <datatype>)
conn oe/oe@pdborcl

SELECT XMLSERIALIZE(CONTENT XMLTYPE('<Owner>Grandco</Owner>'))
FROM dual;
 
XMLTABLE
See XMLTABLE Web Page
 
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. XMLTRANSFORM(<XMLType_instance>, <XMLType_instance) RETURN XMLType;
-- do not run this demo as part of the OE series as the implicit commit in CREATE TABLE will change your OE schema permanently.

conn oe/oe@pdborcl

CREATE TABLE xsl_tab (col1 XMLTYPE);

INSERT INTO xsl_tab
VALUES (
XMLTYPE.createxml('<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output encoding="utf-8"/> <!-- alphabetizes an xml tree -->
<xsl:template match="*"> <xsl:copy>
<xsl:apply-templates select="*|text()">
<xsl:sort select="name(.)" data-type="text" order="ascending"/>
</xsl:apply-templates> </xsl:copy> </xsl:template>
<xsl:template match="text()">
<xsl:value-of select="normalize-space(.)"/>
</xsl:template> </xsl:stylesheet> '));

SELECT XMLTRANSFORM(w.warehouse_spec, x.col1).GetClobVal()
FROM warehouses w, xsl_tab x
WHERE w.warehouse_name = 'San Francisco';

Related Topics
All Functions
Analytic Functions
Character Functions
Collection Functions
CONNECT BY
Conversion Functions
Data Mining Functions
Date Functions
Miscellaneous Functions
Numeric Functions
OLAP Functions
String Functions
Timestamp
XMLQuery
XMLTable

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