Oracle XML Functions
Version 20c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Note: Run these demos top to bottom without using COMMIT or ROLLBACK.
 
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@pdbdev

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@pdbdev

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@pdbdev

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;

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@pdbdev

col docks format a30

SELECT warehouse_name,
EXTRACTVALUE(e.warehouse_spec, '/Warehouse/Docks') "Docks"
FROM warehouses e
WHERE warehouse_spec IS NOT NULL;
 
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@pdbdev

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@pdbdev

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@pdbdev

SELECT SYS_XMLGEN(email)
FROM employees
WHERE employee_id = 205;
 
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@pdbdev

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@pdbdev

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@pdbdev

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@pdbdev

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@pdbdev

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@pdbdev

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 if a given XQuery expression returns a nonempty XQuery sequence XMLEXISTS(XQuery_string PASSING [BY VALUE] expression [AS IDENTIFIER]) RETURN BOOLEAN;
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@pdbdev

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@pdbdev

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@pdbdev

SELECT XMLPI(NAME "Order analysisComp", 'imported, reconfigured, disassembled') AS "XMLPI"
FROM dual;
 
XMLQUERY
See XMLQuery Web Page Link Below
 
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@pdbdev

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@pdbdev

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@pdbdev

SELECT XMLSERIALIZE(CONTENT XMLTYPE('<Owner>Grandco</Owner>'))
FROM dual;
 
XMLTABLE
See XMLTABLE Web Page Link Below
 
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@pdbdev

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
XML Indexes
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx