SQL function XMLSequence returns an XMLSequenceType value (a varray of XMLType instances). Because it returns a collection, this function can be used in the FROM clause of SQL queries.
Example 1:
SELECT value(T).getstringval() Attribute_Value
FROM table(XMLSequence(extract(XMLType('V1V2V3'),
'/A/B'))) T;
Example 2:
CREATE TABLE emp_xml_tab OF XMLType;
Table created.
INSERT INTO emp_xml_tab VALUES( XMLType('
112Joe50000217
Jane
60000412Jack40000'));
1 row created.
COMMIT;
To create a new XML document containing only employees who earn $50,000 or more, you can use the following query:
SELECT sys_XMLAgg(value(em), XMLFormat('EMPLOYEES'))
FROM emp_xml_tab doc, table(XMLSequence(extract(value(doc),
'/EMPLOYEES/EMP'))) em
WHERE extractValue(value(em), '/EMP/SALARY') >= 50000;
These are the steps involved in this query:
Function extract returns a fragment of EMP elements.
Function XMLSequence gathers a collection of these top-level elements into XMLType instances and returns that.
Function table makes a table value from the collection. The table value is then used in the query FROM clause.
Example 3:
XMLSEQUENCE: Generate a Document for Each Row of a Cursor
In this example, SQL function XMLSequence is used to create an XML document for each row of a cursor expression, and it returns an XMLSequenceType value (a varray of XMLType instances).
SELECT value(em).getClobVal() AS "XMLTYPE"
FROM table(XMLSequence(Cursor(SELECT *
FROM hr.employees
WHERE employee_id = 104))) em;
Example 4:
XMLSEQUENCE: Unnesting Collections in XML Documents into SQL Rows
CREATE TABLE dept_xml_tab OF XMLType;
Table created.
INSERT INTO dept_xml_tab
VALUES(
XMLType('Sports
John33333
Jack333444')
Example 1:
SELECT value(T).getstringval() Attribute_Value
FROM table(XMLSequence(extract(XMLType('V1V2V3'),
'/A/B'))) T;
Example 2:
CREATE TABLE emp_xml_tab OF XMLType;
Table created.
INSERT INTO emp_xml_tab VALUES( XMLType('
112Joe50000217
Jane
60000412Jack40000'));
1 row created.
COMMIT;
To create a new XML document containing only employees who earn $50,000 or more, you can use the following query:
SELECT sys_XMLAgg(value(em), XMLFormat('EMPLOYEES'))
FROM emp_xml_tab doc, table(XMLSequence(extract(value(doc),
'/EMPLOYEES/EMP'))) em
WHERE extractValue(value(em), '/EMP/SALARY') >= 50000;
These are the steps involved in this query:
Function extract returns a fragment of EMP elements.
Function XMLSequence gathers a collection of these top-level elements into XMLType instances and returns that.
Function table makes a table value from the collection. The table value is then used in the query FROM clause.
Example 3:
XMLSEQUENCE: Generate a Document for Each Row of a Cursor
In this example, SQL function XMLSequence is used to create an XML document for each row of a cursor expression, and it returns an XMLSequenceType value (a varray of XMLType instances).
SELECT value(em).getClobVal() AS "XMLTYPE"
FROM table(XMLSequence(Cursor(SELECT *
FROM hr.employees
WHERE employee_id = 104))) em;
Example 4:
XMLSEQUENCE: Unnesting Collections in XML Documents into SQL Rows
CREATE TABLE dept_xml_tab OF XMLType;
Table created.
INSERT INTO dept_xml_tab
VALUES(
XMLType('Sports
John33333
Jack333444')
);
1 row created.
INSERT INTO dept_xml_tab
VALUES (
XMLType('Sports
Marlin20000')
1 row created.
INSERT INTO dept_xml_tab
VALUES (
XMLType('Sports
Marlin20000')
);
1 row created.
COMMIT;
SELECT extractValue(OBJECT_VALUE, '/Department/@deptno') AS deptno,
extractValue(value(em), '/Employee/@empno') AS empno,
extractValue(value(em), '/Employee/Ename') AS ename
FROM dept_xml_tab,
table(XMLSequence(extract(OBJECT_VALUE,
'/Department/EmployeeList/Employee'))) em;
1 row created.
COMMIT;
SELECT extractValue(OBJECT_VALUE, '/Department/@deptno') AS deptno,
extractValue(value(em), '/Employee/@empno') AS empno,
extractValue(value(em), '/Employee/Ename') AS ename
FROM dept_xml_tab,
table(XMLSequence(extract(OBJECT_VALUE,
'/Department/EmployeeList/Employee'))) em;
No comments:
Post a Comment