CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle XML Functions
Version 11.1
 Have you seen our new Functions page? If not ... Click Here ... for instant access to 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;
 
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' );

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;
  
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_name, EXTRACT(warehouse_spec,
'/Warehouse/Building/Owner') "Owners"
FROM warehouses
WHERE warehouse_id = 3;

UPDATE warehouses
SET warehouse_spec = INSERTXMLBEFORE(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;
 
PATH
Returns the relative path that leads to the resource specified in the parent condition. PATH(<correlation_integer>)
See the main CONNECT_BY page or the CONNECT_BY definition 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>)
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>)
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 (new in 11g)
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 (missed earlier)
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(
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;
 
XMLEXISTS (new in 11g)
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)
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, then 0 is returned. XMLIsValid(XMLType_inst [, schemaurl [, elem]])
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 (new in 11g)

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. 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, 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)
-- 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
Date Functions
Numeric Functions
CONNECT BY
String Functions
Timestamp
XMLQuery
XMLTable
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [62 users online]    © 2010 psoug.org