Выбор и вставка сложных данных Oracle XMLTYPE

Раньше я писал простые запросы Oracle для извлечения данных XMLTYPE, но этот XML отличается - мне нужно извлекать информацию из атрибутов, дочерних элементов и их соответствующих атрибутов. Я также хотел бы написать инструкцию INSERT (желательно такую, которая может найти наивысшее значение параметра и добавить 1). Рассмотрим следующий 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>

Я могу написать, например, запрос для извлечения всех имен полей:

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

Как мне написать запрос, который может извлекать всю различную информацию в табличной форме? Как мне, например, отобразить это как:

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

... и т.д. Мысли? Я пытался составить запрос, но до сих пор крутит колеса.

Slothario 27.08.2014 источник

Ответы (2)

arrow_upward
2
arrow_downward

В вашем примере данные находятся на нескольких уровнях. У каждого field может быть много option. Итак, вы должны разбить как field, так и option элементы с помощью XMLTable. Во-первых, вы должны разбить field элементов, где option элементов отображаются как XMLType. Затем передайте его второй XMLTable для дальнейшего разбиения.

Настройка данных:

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.

Noel 29.08.2014

arrow_upward
1
arrow_downward

В дополнение к ответу Eat A Peach я разработал несколько запросов INSTEAD OF для вставки и обновления данных. Они все еще находятся в разработке, так что немного грубо, но я решил опубликовать их здесь, если кому-то они могут пригодиться.

  CREATE OR REPLACE VIEW FIELD_OPTIONS AS
  select 
    s.id as space_id, 
    s.prefix_code,
    fields.name AS FIELD_NAME,
    fields.label AS FIELD_LABEL,
    options.value as OPTION_VALUE,
    options.label AS OPTIONS_LABEL,
    options.mapping AS OPTIONS_MAPPING
  FROM
    metadata m,
    spaces s,
    XMLTABLE('/metadata/fields/field'
         PASSING xmltype(m.xml_string)
         COLUMNS name VARCHAR(32) PATH '@name',
                 label VARCHAR(64) PATH '@label',
                 options XMLTYPE PATH 'option') fields,
    XMLTABLE('/option'
        PASSING fields.options
        COLUMNS value NUMBER PATH '@value',
                label VARCHAR(64) PATH '.',
                mapping VARCHAR(64) PATH '@mapping') options
  where
    s.metadata_id = m.id;


  CREATE OR REPLACE TRIGGER INSERT_FIELD_OPTIONS
        INSTEAD OF INSERT ON FIELD_OPTIONS
        FOR EACH ROW
  DECLARE
    field_name VARCHAR(32);
    space_prefix_code VARCHAR(32);
    new_option_num NUMBER;
    new_option_label VARCHAR(64);
    new_option_mapping VARCHAR(64);
  BEGIN
    space_prefix_code:=:NEW.prefix_code;
    field_name:=:NEW.field_name;
    new_option_label:=:NEW.options_label;
    new_option_mapping:=:NEW.options_mapping;

    select --TODO: find out if options.value = 0
      max(options.value)+1 INTO new_option_num
    FROM
      metadata m,
      spaces s,
      XMLTABLE('/metadata/fields/field'
           PASSING xmltype(m.xml_string)
           COLUMNS name VARCHAR(32) PATH '@name',
                   label VARCHAR(64) PATH '@label',
                   options XMLTYPE PATH 'option') fields,
      XMLTABLE('/option'
          PASSING fields.options
          COLUMNS value NUMBER PATH '@value',
                  label VARCHAR(64) PATH '.') options
    where
      s.metadata_id = m.id AND
      s.prefix_code = space_prefix_code AND
      fields.name=field_name;

    update metadata set 
      xml_string=(INSERTCHILDXML(XMLTYPE(xml_string),
        '/metadata/fields/field[@name=''' || field_name || ''']',
        'option',
        XMLTYPE('<option mapping="' || new_option_mapping || '" value="' || TO_CHAR(new_option_num) || '">' || new_option_label || '</option>'))).getclobval() --must convert to clob as it is stored in the database that way
    WHERE
      id =(select metadata_id from spaces where prefix_code = space_prefix_code);
    DBMS_OUTPUT.put_line(CHR(9) || TO_CHAR(SQL%ROWCOUNT)||' rows updated');
  END;
  /

  CREATE OR REPLACE TRIGGER UPDATE_FIELD_OPTIONS
        INSTEAD OF UPDATE ON FIELD_OPTIONS
        FOR EACH ROW
  DECLARE
    field_name VARCHAR(32);
    space_prefix_code VARCHAR(32);
    new_options_label VARCHAR(64);
    new_option_mapping VARCHAR(64);
    new_option_value VARCHAR(64);
  BEGIN
    space_prefix_code:=:NEW.prefix_code;
    field_name:=:NEW.field_name;
    new_options_label:=:NEW.options_label;
    --new_option_mapping:=:NEW.options_mapping; --TODO: add this feature later
    new_option_value:=:NEW.option_value;

    update metadata set 
      xml_string=(UPDATEXML(XMLTYPE(xml_string),
        '/metadata/fields/field[@name=''' || field_name || ''']/option[@value=''' || new_option_value || ''']/text()',
        new_options_label)).getclobval() --must convert to clob as it's stored in the database that way
    WHERE
      id =(select metadata_id from spaces where prefix_code = space_prefix_code);
    DBMS_OUTPUT.put_line(CHR(9) || TO_CHAR(SQL%ROWCOUNT)||' rows updated');
  END;
  /

Slothario 29.08.2014

Выбор и вставка сложных данных Oracle XMLTYPE

Ответы (2)

Вопросы по теме