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???

On 2 Jun 2005 12:45:28 -0700, "stork" <tbandrow@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

Sign up for courses here:
http://www.suite101.com/suiteu/default.cfm/416752
-----------------------------------------------------------

TJB replied to what Lewis C wrote:

<snip>

Thank you very much. What I'm ultimately trying to do is map an XPATH
query to some sort of a vector so that I can subsequently insert or
update into a normal table. I would like ideally to map an XPath query
to a table and then insert into in a bulk way (or use merge), but even
if I have to loop through a sequence of query results and do insert, it
would be a heck of a lot better than passing a bunch of arrays to proc
to mimic "recordness".

On 3 Jun 2005 00:23:35 -0700, "stork" <tbandrow@mightyware.com> wrote:

>
>
>TJB replied to what Lewis C wrote:
>
><snip>
>
>Thank you very much. What I'm ultimately trying to do is map an XPATH
>query to some sort of a vector so that I can subsequently insert or
>update into a normal table. I would like ideally to map an XPath query
>to a table and then insert into in a bulk way (or use merge), but even
>if I have to loop through a sequence of query results and do insert, it
>would be a heck of a lot better than passing a bunch of arrays to proc
>to mimic "recordness".

You may be doing more work than you need to. I would need specific
details to make a specific recomendation but check out DBMS_XMLSave,
DBMS_XMLStore or possibly DBMS_XMLDOM. Some good stuff there.

Thanks,

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

Sign up for courses here:
http://www.suite101.com/suiteu/default.cfm/416752
-----------------------------------------------------------