oracle - 从 XMLTYPE 解析多列

Question

我想从 中选择数据XML 文件使用 Oracle SQL .

这是我的 XML 文件:

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

以下代码是 SQL 我用来提取 XML (在 SQL 查询中缩小):

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;

这是输出:

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

你可以看到第三个 <column> <row> 的 child 输出中缺少，但如果我添加 column3 CLOB PATH 'column[3]/value'到 COLUMNS属性，就会出现。

我的问题是 XML 可以有任意数量的 <column>标签，因为我从一开始就不知道计数，所以我无法定义一个固定的解决方案。

你能帮我告诉我哪个修改应该使我的查询适用于多个 <column>标签？或者哪个查询应该输出 <column>的动态数?

Answer 1

简单的方法

预定义的冗余列数。

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;

旋转结果

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()') 

硬核方法。
您始终可以使用流水线表函数。

1) 找出最大列数
2) 生成动态类型
3) 填充动态类型
4) 两天后享受用 pl sql 编写的最复杂的代码 :)

下面是管道功能的好例子。这并不完全是您所需要的，但您可以在此基础上进行构建

Return dynamic result set from sys_refcursor