Parsing multiple columns from XMLTYPE

Question

I want to select data from a XML file using Oracle SQL.

Here is my XML file:

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

The following code is the SQL I'm using to extract the XML (minified in SQL query):

SELECT
    *
FROM
    XMLTABLE(
        'for $i in worksheet/sheetData/row return $i'
        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
            column1  CLOB PATH 'column[1]/value',
            column2  CLOB PATH 'column[2]/value'
    ) xml;

And this is the output:

---------------------
| COLUMN1 | COLUMN2 |
|---------|---------|
| 0       | 1       |
| 3       | 4       |
| 6       | 7       |
---------------------

You can see that the third <column> child of <row> is missing from the output but if I add column3 CLOB PATH 'column[3]/value' to the COLUMNS property, it will appear.

My problem is that the XML can have any number of <column> tags and since I don't know the count from the beginning, I can't define a fixed solution.

Can you help to tell me which modification should make my query to work for multiple <column> tags? Or which query should output a dynamic number of <column>?

Query sting `'/worksheet/sheetData/row'` is sufficient, you don't need an explicit `for ... in` — Wernfried Domscheit, Aug 20 '18 at 06:46

score 2 · Answer 1 · answered Aug 20 '18 at 07:44

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

Parsing multiple columns from XMLTYPE

1 Answers1