title: Choosing Distinct elements while joining two XMLTables
body: <p>I have a table <code>ATTR_MASTER</code> which has contents as follows:</p>
  
  <pre><code>ID   L_NAME_N  C_LEVEL
  1000    e1        D
  1001    e2        D
  1002    e3        D
  1003    e4        D
  1004    e1        D
  1005    e2        D
  1006    e3        D
  1007    e4        D
  </code></pre>
  
  <p>Now I am executing select query as follows:</p>
  
  <pre><code>SELECT e.ID,x.EVALUE,z.VALUE_TYPE,x.w FROM ATTR_MASTER e, xmltable
   (
   '//B/C/D/*[local-name(.)!=''w'' and text()]'
    PASSING xmltype('&lt;A&gt;&lt;B&gt;&lt;C&gt;&lt;D&gt;&lt;w&gt;1L&lt;/w&gt;&lt;e1&gt;AMAR&lt;/e1&gt;&lt;e2&gt;AKBAR&lt;/e2&gt;&lt;e3&gt;1234&lt;/e3&gt;&lt;e4&gt;BIJAY&lt;/e4&gt;&lt;/D&gt;&lt;/C&gt;&lt;C&gt;&lt;D&gt;&lt;w&gt;1B&lt;/w&gt;&lt;e1&gt;ARTI&lt;/e1&gt;&lt;e2&gt;AKBAR&lt;/e2&gt;&lt;e3&gt;5678&lt;/e3&gt;&lt;e4&gt;BIJAY&lt;/e4&gt;&lt;/D&gt;&lt;/C&gt;&lt;/B&gt;&lt;/A&gt;')
  
   COLUMNS 
  EVALUE VARCHAR2(100) PATH './text()',
  L_NAME_EN VARCHAR2(50) PATH 'local-name(.)',
  w VARCHAR2(20) PATH './parent::*/w'
  
   )x LEFT OUTER JOIN
   XMLTABLE
  
  (
   '//GetDataLookupValuesResponse/tuple/old'
   PASSING xmltype('&lt;GetDataLookupValuesResponse&gt;&lt;tuple&gt;&lt;old&gt;&lt;DataLookup&gt;&lt;Key&gt;e1&lt;/Key&gt;&lt;Value&gt;String&lt;/Value&gt;&lt;/DataLookup&gt;&lt;/old&gt;&lt;/tuple&gt;&lt;tuple&gt;&lt;old&gt;&lt;DataLookup&gt;&lt;Key&gt;e2&lt;/Key&gt;&lt;Value&gt;String&lt;/Value&gt;&lt;/DataLookup&gt;&lt;/old&gt;&lt;/tuple&gt;&lt;tuple&gt;&lt;old&gt;&lt;DataLookup&gt;&lt;Key&gt;e3&lt;/Key&gt;&lt;Value&gt;Number&lt;/Value&gt;&lt;/DataLookup&gt;&lt;/old&gt;&lt;/tuple&gt;&lt;tuple&gt;&lt;old&gt;&lt;DataLookup&gt;&lt;Key&gt;e4&lt;/Key&gt;&lt;Value&gt;String&lt;/Value&gt;&lt;/DataLookup&gt;&lt;/old&gt;&lt;/tuple&gt;&lt;/GetDataLookupValuesResponse&gt;')
  
   COLUMNS 
  VALUE_TYPE VARCHAR2(50) PATH '//Value',
  C_KEY varchar2(50) PATH '//Key'
  )z ON x.L_NAME_EN=z.C_KEY
  
   where e.L_NAME_EN=x.L_NAME_EN;
  </code></pre>
  
  <p>The output shown is</p>
  
  <pre><code>ID     EVALUE VALUE_TYPE  W
  1004    AMAR    String    1L
  1000    AMAR    String    1L
  1005    AKBAR   String    1L
  1001    AKBAR   String    1L
  1006    1234    Number    1L
  1002    1234    Number    1L
  1007    BIJAY   String    1L
  1003    BIJAY   String    1L
  1004    ARTI    String    1B
  1000    ARTI    String    1B
  1005    AKBAR   String    1B
  1001    AKBAR   String    1B
  1006    5678    Number    1B
  1002    5678    Number    1B
  1007    BIJAY   String    1B
  1003    BIJAY   String    1B
  </code></pre>
  
  <p>This is because we have duplicate elements in the column <code>L_NAME_N</code> of the table <code>ATTR_MASTER</code>, that is why in the where clause of the select statement, each element is considered twice and again since in the <code>XML</code>, we have two "w" elements, the value of each element is printed four times in the output.
  But I need the following result,</p>
  
  <pre><code>ID     EVALUE      VALUE_TYPE      W
  1000    AMAR        String         1L
  1004    ARTI        String         1B
  1001    AKBAR       String         1L
  1005    AKBAR       String         1B
  1002    1234        Number         1L
  1006    5678        Number         1B
  1003    BIJAY       String         1L
  1007    BIJAY       String         1B
  </code></pre>
  
  <p>The sequence of the rows may vary in the output which is not a issue, but I need only these rows. Again I cannot change the contents of the table <code>ATTR_MASTER</code>. Also I have tried <code>SELECT DISTINCT...</code>, but it won't work. Where am I wrong?</p>