An INTO clause is expected in this SELECT statement error when fetching from XMLTABLE

Question

I am trying to run a script that involves an xmltable and I am getting a

PLS-00428: an INTO clause is expected in this SELECT statement

How could I correct my script and get 2 rows from the xml?

Oracle 11g

DECLARE xml_char xmltype;
BEGIN
xml_char := xmltype.createXML('<xml><ROWSET><ROW UNIQUEID="All0" ALLOWCHG="0"/><ROW UNIQUEID="All1" ALLOWCHG="1"/></ROWSET></xml>');
select UNIQUEID, ALLOWCHG from xmltable ( '/xml/ROWSET/ROW' passing xml_char columns "UNIQUEID" varchar(30) path '@UNIQUEID', "ALLOWCHG" varchar(30) path '@ALLOWCHG' ) ;
END;

This is a trivial syntax question. Describing yourself as a "noob" doesnt't relieve you of the obligation to read the Oracle documentation. docs.oracle.com/cd/E11882_01/appdev.112/e25519/… — APC
ok, but why is it asking to have those fields (UNIQUEID, ALLOWCHG) put into variables? — evangelus
Because Oracle Pl/SQL requires us to select into variables. It just does. — APC
You have to select into variables because why else are you selecting? It's pointless to select something if you aren't going to do anything with it. — Chris Cameron-Mills

Ro Milton Ro Milton · Accepted Answer · 2013-02-27T02:39:00

In SQL, a select statement never contains an INTO clause.

In PL/SQL, a select statement requires an INTO clause unless it's in cursor. I modified your code to use a cursor:

DECLARE 
xml_char xmltype;

cursor c_cursor is
select UNIQUEID, ALLOWCHG 
from xmltable ( '/xml/ROWSET/ROW' 
                passing xml_char 
                columns 
                    "UNIQUEID" varchar(30) path '@UNIQUEID',
                    "ALLOWCHG" varchar(30) path '@ALLOWCHG'
              );

BEGIN

xml_char := xmltype.createXML('<xml><ROWSET><ROW UNIQUEID="All0" ALLOWCHG="0"/><ROW UNIQUEID="All1" ALLOWCHG="1"/></ROWSET></xml>');

for i in c_cursor loop  
  dbms_output.put_line('UNIQUEID: ' || i.uniqueid || ' ALLOWCHG: '|| i.allowchg);
end loop;

END;

Don't worry, we all make silly mistakes mate, I do despite years of experience.

An INTO clause is expected in this SELECT statement error when fetching from XMLTABLE

1 Answers