Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.