Oracle XML Functions
Version 11.2.0.3
 
Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions
 
APPENDCHILDXML
Note: Run these demos top to bottom without using COMMIT or ROLLBACK.
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

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)
conn oe/oe

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>)
conn oe/oe

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

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

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

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;
  
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

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

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

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

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

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

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

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

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

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

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

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

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;
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

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_INTGER;
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_expr XMLPARSE(<document | content> <value_expression> [WELLFORMED])
conn oe/oe

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_expr XMLPI(<name> <identifier>, <value_expression>)
conn oe/oe

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

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

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 CURSPOR, format>)
TBD
 
XMLSERIALIZE
Creates a string or LOB containing the contents of value_expr. XMLSERIALIZE(<DOCUMENT | CONTENT> <value_expression> AS <datatype>)
conn oe/oe

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

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
Built-in Functions
CONNECT BY
Date Functions
Numeric 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-2013 Daniel A. Morgan All Rights Reserved