Function to return a Ref Cursor from XML (NOT XML from a Ref Cursor)

Is it possible to generate a (weak) cursor from an XML document? There's plenty written about getting XML back from functions, but I already have XML (from various Web Services) and I want to cast it as a Ref Cursor to return to (say) Oracle Forms. The calling program/ client will always know what the cursor columns will be, as they will know what the called web service returns. Something like this:

CREATE OR REPLACE FUNCTION x RETURN SYS_REFCURSOR IS
  v_xml VARCHAR2(4000) :=

    '<?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>' ||
    '</ROWSET>';
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur
  FOR
    SELECT * FROM some_kind_of_cast_of_v_xml;

  RETURN cur;
END;

I know how this kind of approach works with a PL/SQL table type and
use of CAST, but from an XML document? The XML doc can be XMLTYPE,
CLOB, or VARCHAR2, I really don't care, it's just that I want a cursor
showing the various fields as column, and not XML. Even the scalar
types don't matter - they can all be string; let the client program
worry about that.

Thanks,

Ant Received on Thu Jun 07 2007 - 06:34:59 CDT