ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Note: Run these demos top to bottom without using COMMIT or ROLLBACK.
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;
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%';
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;
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;
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';
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
SELECT XMLPARSE(CONTENT '124 <purchaseOrder poNo="12435">
<customerName> Acme Enterprises</customerName>
<itemNo>32987457</itemNo> </purchaseOrder>' WELLFORMED) AS PO
FROM dual;
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.
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>)
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.
