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

From: Romeo Olympia <romeo.olympia_at_gmail.com>
Date: Fri, 08 Jun 2007 03:43:13 -0000
Message-ID: <1181274193.432818.195770@a26g2000pre.googlegroups.com>

On Jun 7, 7:34 pm, ag1969 <monkey_puz..._at_hotmail.com> wrote:
> 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

Look up the documentation for xmlSequence() and extract() / extractValue() functions in the XML Developers Guide. Something like this will get you started:

SELECT

	extract(value(t), '/ROW/EMPNO/text()').getNumberVal() EMPNO,
	extractValue(value(t), '/ROW/ENAME/text()') ENAM,
	extractValue(value(t), '/ROW/JOB/text()') JOB,
	extractValue(value(t), '/ROW/MGR/text()') MGR,
	extractValue(value(t), '/ROW/HIREDATE/text()') HIREDATE,
	extract(value(t), '/ROW/SAL/text()').getNumberVal() SAL,
	extract(value(t), '/ROW/DEPTNO/text()').getNumberVal() 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>' ||
	 '</ROWSET>'

), '/ROWSET/ROW'))) t

HTH. Romeo Received on Thu Jun 07 2007 - 22:43:13 CDT