从复杂的Oracle XMLTYPE数据中选择并插入其中 [英] Selecting from and inserting to complex Oracle XMLTYPE data

问题描述

我之前编写了简单的Oracle查询来提取XMLTYPE数据，但是这种XML是不同的-我需要从属性，子元素及其各自的属性中提取信息.我也想写一个INSERT语句(最好是一个能够找到最高选项值并加1的语句).考虑以下XML:

I've written simple Oracle queries to extract XMLTYPE data before, but this XML is different - I need to pull information from attributes, child elements, and their respective attributes. I also would like to write an INSERT statement as well (preferably one that is able to find the highest option value and add 1). Consider the following XML:

<metadata>
    <fields>
        <field name="cusInt01" label="Reference point">
            <option value="1">CB</option>
            <option value="2">CF</option>
            <option value="3">DF</option>
            <option value="4">EKB</option>
            <option value="5">ES</option>
            <option value="6">GL</option>
            <option value="7">GR</option>
            <option value="8">KB</option>
            <option value="9">KBE</option>
            <option value="10">MSL</option>
            <option value="11">PT</option>
            <option value="12">RB</option>
            <option value="13">RF</option>
            <option value="14">RT</option>
            <option value="15">UN</option>
            <option value="16">UNK</option>
        </field>
    </fields>
</metadata>

例如，我可以编写查询以提取所有字段名称:

I can write, for instance, a query to extract all of the field names:

select 
  field_names.*
FROM
  metadata m,
  XMLTABLE('/metadata/fields/field'
       PASSING xmltype(m.xml_string)
       COLUMNS field_name VARCHAR(32) PATH '@name') field_names;

如何编写查询以表格形式提取所有不同的信息?例如，如何将其显示为:

How do I write a query that can extract all the different information in a tabular form? How do I, for instance, display it as:

field_name   |   field_label       |   option_value   |   option_label
cusInt01         Reference point       1                  CB
cusInt01         Reference point       2                  CF
cusInt01         Reference point       2                  DF

...等.有什么想法吗?我一直在尝试将一个查询拼凑在一起，但是到目前为止，我的方向盘还是旋转的.

... etc. Thoughts? I've been trying to cobble a query together but so far spinning my wheels.

推荐答案

在您的示例中，数据处于多个级别.每个field可以具有多个option. 因此，必须使用XMLTable分解field和option元素. 首先，您应该破坏field元素，其中option元素映射为XMLType.然后将其传递给第二个XMLTable，以进一步分解它.

In your example, the data is at multiple levels. Each field can have many option. So, you must break up field as well as option elements with XMLTable. First, you should break field elements, where option elements are mapped as XMLType. Then pass it to second XMLTable to further break it down.

数据设置:

CREATE TABLE metadata (xml_string VARCHAR2 (2000));

INSERT INTO metadata
     VALUES ('<metadata>
    <fields>
        <field name="cusInt01" label="Reference point">
            <option value="1">CB</option>
            <option value="2">CF</option>
            <option value="3">DF</option>
            <option value="4">EKB</option>
            <option value="5">ES</option>
            <option value="6">GL</option>
            <option value="7">GR</option>
            <option value="8">KB</option>
            <option value="9">KBE</option>
            <option value="10">MSL</option>
            <option value="11">PT</option>
            <option value="12">RB</option>
            <option value="13">RF</option>
            <option value="14">RT</option>
            <option value="15">UN</option>
            <option value="16">UNK</option>
        </field>
    </fields>
</metadata>');

COMMIT;

查询:

SELECT field.field_name,
       field.field_label,
       options.option_value,
       options.option_label
  FROM metadata m,
       XMLTABLE (
          'metadata/fields/field'
          PASSING xmltype (m.xml_string)
          COLUMNS field_name VARCHAR2 (32) PATH '@name',
                  field_label VARCHAR2 (32) PATH '@label',
                  field_options XMLTYPE PATH 'option') field,
       XMLTABLE (
          'option'
          PASSING field.field_options
          COLUMNS option_value NUMBER PATH '@value',
                  option_label VARCHAR2 (10) PATH '/') options;

结果:

FIELD_NAME    FIELD_LABEL        OPTION_VALUE    OPTION_LABEL
-------------------------------------------------------------
cusInt01      Reference point    1               CB           
cusInt01      Reference point    2               CF           
cusInt01      Reference point    3               DF           
cusInt01      Reference point    4               EKB          
cusInt01      Reference point    5               ES           
cusInt01      Reference point    6               GL           
cusInt01      Reference point    7               GR           
cusInt01      Reference point    8               KB           
cusInt01      Reference point    9               KBE          
cusInt01      Reference point    10              MSL          
cusInt01      Reference point    11              PT           
cusInt01      Reference point    12              RB           
cusInt01      Reference point    13              RF           
cusInt01      Reference point    14              RT           
cusInt01      Reference point    15              UN           
cusInt01      Reference point    16              UNK 

《 Oracle XML DB开发人员指南》中的类似示例.

这篇关于从复杂的Oracle XMLTYPE数据中选择并插入其中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！