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.