Getting invalid datatype error while parsing xml in Oracle

Question

I am trying to parse XML in PL SQL. But getting following error

Error report:
ORA-06550: line 21, column 43:
PL/SQL: ORA-00902: invalid datatype
ORA-06550: line 17, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I am trying to run below query

DECLARE
l_xml xmltype:= xmltype('<Table>
   <Data>
      <DataKey>Test</DataKey>
      <DataTypeID>8</DataTypeID>
      <DataValue>test1</DataValue>
      <Id>eb96c9c1-6236-403d-9afa-6d45ec623dbc</Id>
   </Data>
</Table>');
begin

select * FROM 
xmltable ('/Table/Data' passing l_xml
         columns
          DataKey nvarchar2(50) path '/Data/DataKey'
          ,DataTypeID int, path '/Data/DataTypeID'       
         , DataValue nvarchar2(1024) path '/Data/DataValue');

end;
/

I am new to PL/SQL, so not able to figure out the error. Can anybody help me here? Thanks

Alex Poole · Accepted Answer · 2014-06-11 14:37:35Z

1

You have an extra comma between int and path:

      ,DataTypeID int, path '/Data/DataTypeID'

should be

      ,DataTypeID int path '/Data/DataTypeID'

Or you could use number instead of int.

Your select also has to be selecting into something, since this is in a PL/SQL context. So now you'll get a PLS-00428 error. Without knowing what you plan to do with the results it isn't entirely obvious how you should correct that - whether you don't actually need PL/SQL, or you want to loop over the results to do something with them, or insert into another table, or...

answered Jun 11, 2014 at 14:37

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashwani K Over a year ago

Actually this is part of a bigger query. I was trying to locate the error. Thanks a lot

Alex Poole Over a year ago

OK, then ignore that part, and +1 for isolating the problem and only showing the relevant part of the query *8-)

Collectives™ on Stack Overflow

Getting invalid datatype error while parsing xml in Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related