title: Selecting from and inserting to complex Oracle XMLTYPE data
body: <p>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:</p>
  
  <pre><code>&lt;metadata&gt;
      &lt;fields&gt;
          &lt;field name="cusInt01" label="Reference point"&gt;
              &lt;option value="1"&gt;CB&lt;/option&gt;
              &lt;option value="2"&gt;CF&lt;/option&gt;
              &lt;option value="3"&gt;DF&lt;/option&gt;
              &lt;option value="4"&gt;EKB&lt;/option&gt;
              &lt;option value="5"&gt;ES&lt;/option&gt;
              &lt;option value="6"&gt;GL&lt;/option&gt;
              &lt;option value="7"&gt;GR&lt;/option&gt;
              &lt;option value="8"&gt;KB&lt;/option&gt;
              &lt;option value="9"&gt;KBE&lt;/option&gt;
              &lt;option value="10"&gt;MSL&lt;/option&gt;
              &lt;option value="11"&gt;PT&lt;/option&gt;
              &lt;option value="12"&gt;RB&lt;/option&gt;
              &lt;option value="13"&gt;RF&lt;/option&gt;
              &lt;option value="14"&gt;RT&lt;/option&gt;
              &lt;option value="15"&gt;UN&lt;/option&gt;
              &lt;option value="16"&gt;UNK&lt;/option&gt;
          &lt;/field&gt;
      &lt;/fields&gt;
  &lt;/metadata&gt;
  </code></pre>
  
  <p>I can write, for instance, a query to extract all of the field names:</p>
  
  <pre><code>select 
    field_names.*
  FROM
    metadata m,
    XMLTABLE('/metadata/fields/field'
         PASSING xmltype(m.xml_string)
         COLUMNS field_name VARCHAR(32) PATH '@name') field_names;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>field_name   |   field_label       |   option_value   |   option_label
  cusInt01         Reference point       1                  CB
  cusInt01         Reference point       2                  CF
  cusInt01         Reference point       2                  DF
  </code></pre>
  
  <p>... etc. Thoughts? I've been trying to cobble a query together but so far spinning my wheels. </p>