SQL> set long 500000

or some big number.  just a sqlplus setting.

search for

xmlsave

on this site. 

because the rowset is gone by then... always helpful to just dump the query out and see what it is giving you back.  I started with "select *" but backed off to just select column_value for showing you what is there:


ops$tkyte@ORA920> SELECT         column_value
  2  FROM xmltest t,
  3  TABLE(XMLSequence(t.xmlpayload.extract('ROWSET/ROW'))) d;

COLUMN_VALUE
-----------------------------------------------------------------------------------------------------------------------------------
<ROW>
  <IDFOBETSTATE>A</IDFOBETSTATE>
  <ISACTIVE>-1</ISACTIVE>
  <INTERNALORDER>1</INTERNALORDER>
  <INTERNALDESCRIPTION>Bet attempted (not open/valid)</INTERNALDESCRIPTION>
  <NAME>Attempted</NAME>
</ROW>

<ROW>
  <IDFOBETSTATE>O</IDFOBETSTATE>
  <ISACTIVE>-1</ISACTIVE>
  <INTERNALORDER>2</INTERNALORDER>
  <INTERNALDESCRIPTION>Bet opened</INTERNALDESCRIPTION>
  <NAME>Open</NAME>
</ROW>

<ROW>
  <IDFOBETSTATE>C</IDFOBETSTATE>
  <ISACTIVE>-1</ISACTIVE>
  <INTERNALORDER>3</INTERNALORDER>
  <INTERNALDESCRIPTION>Bet closed(All legs closed - not settled)</INTERNALDESCRIPTION>
  <NAME>Closed</NAME>
</ROW>

<ROW>
  <IDFOBETSTATE>S</IDFOBETSTATE>
  <ISACTIVE>-1</ISACTIVE>
  <INTERNALORDER>4</INTERNALORDER>
  <INTERNALDESCRIPTION>Bet settled(Bet is paid out)</INTERNALDESCRIPTION>
  <NAME>Settled</NAME>
</ROW>

<ROW>
  <IDFOBETSTATE>?</IDFOBETSTATE>
  <ISACTIVE>-1</ISACTIVE>
  <INTERNALORDER>5</INTERNALORDER>
  <INTERNALDESCRIPTION>Bet undefined(whatever it means)</INTERNALDESCRIPTION>
  <NAME>Unknown</NAME>
</ROW>

<b>so, what you want is:</b>


ops$tkyte@ORA920>
ops$tkyte@ORA920> SELECT
  2  extract(value(d),'/ROW/IDFOBETSTATE/text()').getstringVal() as
  3  IDFOBETSTATE,
  4  extract(value(d),'/ROW/INTERNALORDER/text()').getnumberVal() as
  5  INTERNALORDER
  6  FROM xmltest t,
  7  TABLE(XMLSequence(t.xmlpayload.extract('ROWSET/ROW'))) d;

IDFOBETSTATE    INTERNALORDER
--------------- -------------
A                           1
O                           2
C                           3
S                           4
?                           5

ops$tkyte@ORA920> 

I asked Sean Dillon, our local XML guy, to take a look at this and here's what he had to say:
-------

Hiya Raj,

I'm not sure why you're getting this error, but a couple things you can try:

1.  Make sure you have the appropriate SELECT grant on SCOTT.EMP.
2.  Try the same statement but specify the columns instead of "*". For example, "select empno, ename, job from scott.emp".  See what that gives you.
3.  Make sure you can use DBMS_XMLGEN.GETXML on a simple table you create:

SQL> create table t( txt varchar2(50), num number );
Table created.

SQL> insert into t values ( 'One', 1 );
1 row created.

SQL> insert into t values ( 'Two', 2 );
1 row created.

SQL> insert into t values ( 'Three', 3 );
1 row created.

SQL> insert into t values ( 'Four', 4 );
1 row created.

SQL> select dbms_xmlgen.getxml( 'select * from t' )
  2    from dual;

DBMS_XMLGEN.GETXML('SELECT*FROMT')
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TXT>One</TXT>
  <NUM>1</NUM>
 </ROW>
 <ROW>
  <TXT>Two</TXT>
  <NUM>2</NUM>
 </ROW>
 <ROW>
  <TXT>Three</TXT>
  <NUM>3</NUM>
 </ROW>
 <ROW>
  <TXT>Four</TXT>
  <NUM>4</NUM>
 </ROW>
</ROWSET>

If you have the appropriate privileges (you can just "select * from scott.emp" logged in as the same user...), and the test above works just dandy, let me know.