Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Function to return a Ref Cursor from XML (NOT XML from a Ref Cursor)

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

From: ag1969 <monkey_puzzle_at_hotmail.com>
Date: Tue, 12 Jun 2007 04:49:39 -0700
Message-ID: <1181648979.573102.12230@i13g2000prf.googlegroups.com>


On Jun 8, 4:43 am, Romeo Olympia <romeo.olym..._at_gmail.com> wrote:
> 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

Romeo,

Thanks very much, that's really (very close to) what I was looking for. Because the calling client program will always know what it's expecting in the output it can pass in information about which columns to extractValue from.

Again, thank you.

Ant Received on Tue Jun 12 2007 - 06:49:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US