XMLSEQUENCE Function
✓/XMLTypeJnstance\
^ sysjefcursorjnstaiice)-»-
The XMLSequence() function has two forms
■ The first form inputs an XMLType instance and returns a VARRAY of top-level nodes. This form can be used to shred XML fragments into multiple rows.
■ The second form takes as input a REFCURSOR argument, with an optional instance of the XMLFormat object and returns the varray of XMLTypes corresponding to each row of the cursor. This form can be used to construct XMLType instances from arbitrary SQL queries. Note that in this release, this use of XMLFormat does not support XML schemas.
XMLSequence() is essential for effective SQL queries involving XMLTypes.
Example 10-8 XMLSequence(): Generating One XML Document from Another
Suppose you had the following XML document containing employee information:
<EMPNO>112</EMPNO> <EMPNAME>Joe</EMPNAME> <SALARY>50000</SALARY> </EMP> <EMP>
<EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> <SALARY>60000</SALARY> </EMP> <EMP>
<EMPNO>412</EMPNO>7 <EMPNAME>Jack</EMPNAME> <SALARY>40000</SALARY> </EMP>
</EMPLOYEES>
To create a new XML document containing only those employees who make $50,000
or more for each year, you can use the following syntax:
SELECT SYS_XMLAGG(value(e), xmlformatCEMPLOYEES'))
FROM TABLE(XMLSequence(Extract(doc, '/EMPLOYEES/EMP'))) e WHERE EXTRACTVALUE(value(e), '/EMP/SALARY') >= 50000;
This returns the following XML document:
<EMPNO>112</EMPNO>
<EMPNAME>Joe</EMPNAME> <SALARY>50000</SALARY> </EMP> <EMP>
<EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> <SALARY>60000</SALARY> </EMP> </EMPLOYEES>
Notice how XMLExtract() was used to extract out all the employees:
1. XMLExtract() returns a fragment of EMP elements.
2. XMLSequence() creates a collection of these top level elements into XMLType instances and returns that.
3. The TABLE function was then used to makes the collection into a table value which can be used in the FROM clause of queries.
Example 10-9 XMLSequence(): Generating An XML Document for Each Row of a Cursor Expression, Using SYS_REFCURSOR Argument
Here XMLSequence() creates an XML document for each row of the cursor expression and returns the value as an XMLSequenceType. The XMLFormat object can be used to influence the structure of the resulting XML documents. For example, a call such as:
SELECT value(e).getClobVal()
FROM TABLE(XMLSequence(Cursor(SELECT * FROM emp))) e;
might return the following XML:
XMLType
<EMPNO>300</EMPNO> <ENAME>John</ENAME> </ROW>
<EMPNO>413</EMPNO> <ENAME>Jane</ENAME> </ROW>
<EMPNO>968</EMPNO> <ENAME>Jack</ENAME> </ROW>
The row tag used for each row can be changed using the XMLFormat object.
Example 10-10 XMLSequence(): Unnesting Collections inside XML Documents into SQL Rows
XMLSequence() being a TABLE function, can be used to unnest the elements inside an XML document. If you have a XML documents such as:
<Department deptno="100">
<DeptName>Sports</DeptName> <EmployeeList>
<Employee empno="200"> <Ename>John</Ename> <Salary>33333</Salary> </Employee>
<Employee empno="300"> <Ename>Jack</Ename> <Salary>333444</Salary> </Employee> </EmployeeList> </Department>
<Department deptno="200"> <DeptName>Garment</DeptName> <EmployeeList>
<Employee empno="400"> <Ename>Marlin</Ename> <Salary>20000</Salary> </Employee> </EmployeeList> </Department>
stored in an XMLType table dept_xml_tab, you can use the XMLSequence() function to unnest the Employee list items as top level SQL rows:
CREATE TABLE dept_xml_tab OF XMLTYPE;
INSERT INTO dept_xml_tab VALUES( xmltype('<Department deptno="100">
<DeptName>Sports</DeptName><EmployeeList>
<Employee empno="200"><Ename>John</Ename><Salary>33333</Salary></Employee>
<Employee empno="300"><Ename>Jack</Ename><Salary>333444</Salary></Employee> </EmployeeList></Department>'));
INSERT INTO dept_xml_tab VALUES ( xmltype('<Department deptno="200">
<DeptName>Sports</DeptName><EmployeeList>
<Employee empno="400"><Ename>Marlin</Ename><Salary>20000</Salary></Employee> </EmployeeList></Department>'));
SELECT extractvalue(value(d),//Department/®deptno/) as deptno, extractvalue(value(e),'/Employee/®empno') as empno, extractvalue(value(e),'/Employee/Ename') as ename FROM dept_xml_tab d, TABLE(XMLSequence(extract(value(d),'/Department/EmployeeList/Employee/))) e;
This returns the following: DEPTNO EMPNO ENAME
100 200 John
100 300 Jack
200 400 Marlin
3 rows selected
For each row in table dept_xml_tab, the TABLE function is evaluated. Here, the extract() function creates a new XMLType instance that contains a fragment of all employee elements. This is fed to the XMLSequence() which creates a collection of all employees.
The TABLE function then explodes the collection elements into multiple rows which are correlated with the parent table dept_xml_tab. Thus you get a list of all the parent dept_xml_tab rows with the associated employees.
The extractValue() functions extract out the scalar values for the department number, employee number, and name.
Post a comment