Is it possible to use Oracle XMLTABLE and %type column type selector? I am creating a stored procedure which gets an xml input and inserts the data to a table using XMLTABLE.

I want to know how can i specify a field type according to a table column type when passing columns in XMLTABLE. (using something like %type).

Here is my simplified code:

SELECT tmp.*
FROM (SELECT XMLTYPE(xml_in) AS xml FROM dual),
      XMLTable('for $i in /rowset/row return $i'
                PASSING xml COLUMNS
                  name MYTABLE.name%TYPE path 'name', // %TYPE just doesnt work here :(
                  family MYTABLE.family%TYPE path 'family'
              ) tmp
You can't use the %TYPE construct in the COLUMNS clause.

However, depending on what you do with your data, it may be a good idea to not be too strict with it in this query. For instance, if we take the data from your previous question, note that the following doesn't result in an error:

SQL&gt; SELECT x.*
  2    FROM XMLTable('for $i in /rowset/row return $i'
  3                   passing XMLTYPE(:xml)
  4                   columns
  5                      name   varchar2(1) path 'name',
  6                      family varchar2(1) path 'family'
  7                 ) x;

NAME FAMILY
---- ------
t    m
m    s


Whereas in most case if you have an XML file as input and a column is larger than specified in the table, this would be considered an anomaly.

This is why you could, if you intend to perform validation on the input file, load all columns as text (VARCHAR2(4000)) and then insert into your staging table. This will result into an appropriate error.