title: How to extract element-path from XMLType Node?
body: <p>I would like to have a select statement on an XML document and one column should return me the  <strong>path of each node</strong>.</p>
  
  <p>For example, given the data</p>
  
  <pre><code>SELECT * 
  FROM TABLE(XMLSequence(
    XMLTYPE('&lt;?xml version="1.0"?&gt;
      &lt;users&gt;&lt;user&gt;&lt;name&gt;user1&lt;/name&gt;&lt;/user&gt;
             &lt;user&gt;&lt;name&gt;user2&lt;/name&gt;&lt;/user&gt;
             &lt;group&gt;
                &lt;user&gt;&lt;name&gt;user3&lt;/name&gt;&lt;/user&gt;
             &lt;/group&gt;
             &lt;user&gt;&lt;name&gt;user4&lt;/name&gt;&lt;/user&gt;
      &lt;/users&gt;').extract('/*//*[text()]'))) t;
  </code></pre>
  
  <p>Which results in</p>
  
  <pre><code>column_value
  --------
  &lt;user&gt;&lt;name&gt;user1&lt;/name&gt;&lt;/user&gt;
  &lt;user&gt;&lt;name&gt;user2&lt;/name&gt;&lt;/user&gt;
  &lt;user&gt;&lt;name&gt;user3&lt;/name&gt;&lt;/user&gt;
  &lt;user&gt;&lt;name&gt;user4&lt;/name&gt;&lt;/user&gt;
  </code></pre>
  
  <p>I'd like to have a result like this:</p>
  
  <pre><code>path                     value
  ------------------------ --------------
  /users/user/name         user1
  /users/user/name         user2
  /users/group/user/name   user3
  /users/user/name         user4
  </code></pre>
  
  <p>I can not see how to get to this. I figure there are two thing that have to work together properly:</p>
  
  <ul>
  <li><strong>Can I extract the <code>path</code> from an <code>XMLType</code> with a single operation or method</strong>, or do I have to do this with string-magic?</li>
  <li><strong>What is the correct XPath expression so that I do get the whole element path</strong> (if thats possible), eg. <code>&lt;users&gt;&lt;group&gt;&lt;user&gt;&lt;name&gt;user3&lt;/name&gt;&lt;/user&gt;&lt;/group&gt;&lt;/user&gt;</code> insead of <code>&lt;user&gt;&lt;name&gt;user3&lt;/name&gt;&lt;/user&gt;</code>?</li>
  </ul>
  
  <p>Maybe I am not understanding <code>XMLType</code> fully, yet. It could be I need a different approach, but I can not see it.</p>
  
  <p>Sidenotes:</p>
  
  <ul>
  <li>In the final version the XML document will be coming from CLOBs of a table, not a static document.</li>
  <li>The <code>path</code> column can of course also use dots or whatever and the initial slash is not the issue, any representation would do.</li>
  <li>Also I would not mind if every inner node also gets a result row (possibly with <code>null</code> as <code>value</code>), not only the ones with <code>text()</code> in it (which is what I am really interested in).</li>
  <li>In the end I will need the <em>tail element</em> of <code>path</code> separate (always <code>"name"</code> in the example here, but this will vary later), i.e. <code>('/users/groups/user', 'name', 'user3')</code>, I can deal with that separately.</li>
  </ul>