xml to table

Hello, I know this code will convert on the fly an XML document to a table:

SELECT

  xml.extract('//EMPNO/text()')    as empno,
  xml.extract('//ENAME/text()')    as ename,
  xml.extract('//JOB/text()')      as job,
  xml.extract('//MGR/text()')      as mgr,
  xml.extract('//HIREDATE/text()') as hiredate,
  xml.extract('//SAL/text()')      as sal,
  xml.extract('//DEPTNO/text()')   as deptno
from table(xmlSequence(extract(xmlType(
	 '<?xml version = ''1.0''?>' ||
	 '<ROWSET>' ||
	 '   <ROW num="1">' ||


	 '      <EMPNO>7369</EMPNO>' ||

	 '      <ENAME>SMITH</ENAME>' ||

	 '      <JOB>CLERK</JOB>' ||

	 '      <MGR>7902</MGR>' ||

	 '      <HIREDATE>12/17/1980 0:0:0</HIREDATE>' ||

	 '      <SAL>800</SAL>' ||

	 '      <DEPTNO>20</DEPTNO>' ||

	 '   </ROW>' ||
	 '   <ROW num="2">' ||


	 '      <EMPNO>7499</EMPNO>' ||

	 '      <ENAME>ALLEN</ENAME>' ||

	 '      <JOB>SALESMAN</JOB>' ||

	 '      <MGR>7698</MGR>' ||

	 '      <HIREDATE>2/20/1981 0:0:0</HIREDATE>' ||

	 '      <SAL>1600</SAL>' ||

	 '      <COMM>300</COMM>' ||

	 '      <DEPTNO>30</DEPTNO>' ||

	 '   </ROW>' ||
	 '   <ROW num="3">' ||


	 '      <EMPNO>42</EMPNO>' ||

	 '      <ENAME>YOSSSARIAN</ENAME>' ||

	 '      <JOB>PILOT</JOB>' ||

	 '      <MGR>22</MGR>' ||

	 '      <HIREDATE>2/20/1981 0:0:0</HIREDATE>' ||

	 '      <SAL>1600</SAL>' ||

	 '      <COMM>300</COMM>' ||

	 '      <DEPTNO>30</DEPTNO>' ||

	 '   </ROW>' ||
	 '</ROWSET>'

I'm wondering if there's a way (Oracle 10.2) to replace the EXTRACT calls with a single magic CAST that would parse the <ROW>...</ROW> XML fragment as a whole...

Thank you.

Kind regards, Cristian Received on Tue Jul 29 2008 - 06:51:40 CDT