Re: Help with PL/SQL and XmlType

From: Lewis C <lewisc_at_excite.com>
Date: Fri, 03 Jun 2005 00:18:44 GMT
Message-ID: <bg7v9191u1ag5dssoiveqjio44sqa2siug@4ax.com>

On 2 Jun 2005 12:45:28 -0700, "stork" <tbandrow_at_mightyware.com> wrote:

>
>Why does this?
>
>declare
> var XMLType;
> var2 XMLType;
> buf varchar2(2000);
> type refcur is ref cursor;
> rc refcur;
>begin
> var := xmltype('<PERSON> <NAME ike="uno"> ramesh </NAME> <NAME
>ike="duo"> foo </NAME> </PERSON>');
> var2 := var.extract( '/PERSON/NAME[@ike="uno"]' );
>
> dbms_output.put_line( 'hello' );
> OPEN rc FOR
> SELECT extract(value(xooty), '//NAME/text()').getStringVal() AS
>empno
> FROM table(xmlsequence(extract(var, '/PERSON'))) xooty;
> LOOP
> fetch rc into buf;
> dbms_output.put_line( buf );
> exit when rc%notfound;
> END LOOP;
>end;
>
>return this:
>
>ramesh foo
>ramesh foo
>
>I wasn't expecting ramesh and foo to be combined on each row???

What were you expecting? Ramesh on one row and foo on another?

First of all that's not what you're asking for. You need to move some code:

LOOP
    fetch rc into buf;
    dbms_output.put_line( buf );
    exit when rc%notfound;
  END LOOP; Should be

LOOP
    fetch rc into buf;
    exit when rc%notfound;
    dbms_output.put_line( buf );
  END LOOP; Now you're actually seeing the correct amount of data.

What exactly do you want to see as output?

In the line: extract(value(xooty), '//NAME/text()').getStringVal() AS empno

You aren't specifying a specific NAME. Type, extract(value(xooty), '//NAME[1]/text()').getStringVal() AS empno

You'll get ramesh on a line by itself. Is that along the lines of what you were looking for?

Hope that helps,

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Thu Jun 02 2005 - 19:18:44 CDT