title: Oracle extract values from xmltype
body: <p>This is the code I am currently using:</p>
  
  <pre><code>SET serveroutput ON
  CREATE OR REPLACE
  PROCEDURE test_proc(i_xml varchar2)
  IS
  
  l_name VARCHAR2(20);
  l_age NUMBER;
  l_xml xmltype;
  BEGIN
  l_xml := xmltype(i_xml);
  
  
  FOR x IN
  (SELECT VALUE(p) col_val
  FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml, '/ROWSET/ROW'))) p
  )
  LOOP
  
       IF x.col_val.existSNode('/ROW/name/text()') &gt; 0 THEN
            l_name:= x.col_val.EXTRACT('/ROW/name/text()').getstringVal();
       END IF;
       IF x.col_val.existSNode('/ROW/age/text()') &gt; 0 THEN
            l_age := x.col_val.EXTRACT('/ROW/age/text()').getstringVal();
       END IF;
  end loop;
  
  end;
  /
  BEGIN
  test_proc('&lt;ROWSET&gt;
  &lt;ROW&gt;
  &lt;name&gt;aa&lt;/name&gt;
  &lt;age&gt;20&lt;/age&gt;
  &lt;/ROW&gt;
  &lt;ROW&gt;
  &lt;name&gt;bbb&lt;/name&gt;
  &lt;age&gt;25&lt;/age&gt;
  &lt;/ROW&gt;
  &lt;/ROWSET&gt;');
  END;
  /
  </code></pre>
  
  <p>The above code uses xml to extract and save the existing node values to particular local variables. It is been used in the case for multiple sets of data and is working fine. I just wanted to know whether can I able to use the same without "for x loop", because I will only have one data in the i_xml from now onwards and I will only have either
  <code>name</code> or <code>age</code> tags .</p>
  
  <p>The following code should be used to save into l_name or l_age without the "loop" method like I used above:</p>
  
  <pre><code>&lt;ROWSET&gt;
  &lt;ROW&gt;
      &lt;name&gt;aa&lt;/name&gt;
  &lt;/ROW&gt;
  &lt;/ROWSET&gt;
  </code></pre>
  
  <p>or </p>
  
  <pre><code>&lt;ROWSET&gt;
  &lt;ROW&gt;
      &lt;age&gt;18&lt;/age&gt;
  &lt;/ROW&gt;
  &lt;/ROWSET&gt;
  </code></pre>
  
  <p>/
  And I've tried using the following:</p>
  
  <pre><code>SELECT
       CASE
            WHEN VALUE(p).existsNode('/ROW/name/text()') = 1
            THEN p.EXTRACT('/ROW/name/text()').getstringVal()
            WHEN VALUE(P).existsNode('/ROW/age/text()') = 1
            THEN p.EXTRACT('/ROW/age/text()').getstringVal()
       END
  INTO l_new
  FROM TABLE(xmlsequence(EXTRACT(l_xml, '/ROWSET/ROW'))) p;
  </code></pre>
  
  <p>/
  Any better way is appreciated.. Thanks</p>