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