Parsing multiple columns from XMLTYPE

Easy ways

Predefined redundant amount of columns.

SELECT    *FROM    XMLTABLE(        '/worksheet/sheetData/row'        PASSING XMLTYPE('<worksheet><sheetData><row><column><value>0</value></column><column><value>1</value></column><column><value>2</value></column></row><row><column><value>3</value></column><column><value>4</value></column><column><value>5</value></column></row><row><column><value>6</value></column><column><value>7</value></column><column><value>8</value></column></row></sheetData></worksheet>')        COLUMNS            column_cnt number path 'count(column)',            column1  varchar2(4000) PATH 'column[1]/value',            column2  varchar2(4000) PATH 'column[2]/value',          --etc. ...            column10   varchar2(4000) path 'column[10]/value'    ) xml;

Pivot the result

SELECT    row_nr,    col_nr,    col_valueFROM    XMLTABLE(        '/worksheet/sheetData/row'        PASSING XMLTYPE('<worksheet><sheetData><row><column><value>0</value></column><column><value>1</value></column><column><value>2</value></column></row><row><column><value>3</value></column><column><value>4</value></column><column><value>5</value></column></row><row><column><value>6</value></column><column><value>7</value></column><column><value>8</value></column></row></sheetData></worksheet>')        COLUMNS                    columns_xml   xmltype path '.',                    row_nr  FOR ORDINALITY     ) xml    ,xmltable('row/column' passing columns_xml              columns                     col_nr for ordinality,                    col_value varchar2(10) path './value/text()') 

Hardcore approach.Alway you can use pipelined table function.

1) Find max count of columns
2) Generate dynamic type
3) Populate dynamic type
4) Two days later enjoy the most sophisticated code written in pl sql :)

Good example of pipelline function below. This is not exactly what you need but you can build on it

Return dynamic result set from sys_refcursor