Selecting from and inserting to complex Oracle XML

Question

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.

Answer 1

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.

Data Setup:

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;

Query:

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;

Result:

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 

Similar example at Oracle XML DB Developer's Guide.

Answer 2

In addition to Eat A Peach's answer, I developed some INSTEAD OF queries to insert and update data. They're still in development so a tad rough but I figured I'd post them here in case someone might find them handy.

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