Search the Reference Library pages:  

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 |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----