title: Oracle SQL Order By with XML and Union ALL
body: <p>I wrote a select statement like this:</p>
  
  <pre><code>Select 
   xmlagg(xmlelement( "Species", 
                    xmlelement("Type",Type),
                    xmlelement("Name",Name),
                    xmlforest(case when Tail is not null then Tail else null end "Trait")))
  From Table_Animals
  Order By Name;
  </code></pre>
  
  <p>This is my input </p>
  
  <p><a href="https://i.stack.imgur.com/JVrRQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JVrRQ.png" alt="enter image description here"></a></p>
  
  <p>The query runs fine, but when I added an UNION ALL like so:</p>
  
  <pre><code> Select 
   xmlagg(xmlelement( "Species", 
                    xmlelement("Type",Type),
                    xmlelement("Name",Name),
                    xmlforest(case when Tail is not null then Tail else null  end "Trait")))
   From Table_Animals
  
  UNION ALL 
  
   Select 
   xmlagg(xmlelement( "Species", 
                    xmlelement("Type",Type),
                    xmlelement("Name",Name),
                    xmlforest(case when Teeth is not null then Teeth else null end "Trait")))
   From Table_Animals
   Where Prey is not null
   Order By Name;
  </code></pre>
  
  <p>I got the error : <code>ORA-00904: "Name": invalid identifier</code></p>
  
  <p>How do I use Order By correctly in the above example?</p>
  
  <p>My desired output is:</p>
  
  <pre><code>    &lt;Species&gt;
      &lt;Type&gt;Bird&lt;Type&gt;
      &lt;Name&gt;Eagle&lt;Name&gt;
      &lt;Species&gt;
      &lt;Species&gt;
      &lt;Type&gt;Mammal&lt;Type&gt;
      &lt;Name&gt;Fox&lt;Name&gt;
      &lt;Trait&gt;Sharp&lt;Trait&gt;
      &lt;Species&gt;
      &lt;Species&gt;
      &lt;Type&gt;Plant&lt;Type&gt;
      &lt;Name&gt;Lettuce&lt;Name&gt;
      &lt;Species&gt;
      &lt;Species&gt;
      &lt;Type&gt;Mammal&lt;Type&gt;
      &lt;Name&gt;Rabbit&lt;Name&gt;
      &lt;Trait&gt;Short&lt;Trait&gt;
      &lt;Species&gt;
      &lt;Species&gt;
      &lt;Type&gt;Mammal&lt;Type&gt;
      &lt;Name&gt;Rabbit&lt;Name&gt;
      &lt;Trait&gt;Dull&lt;Trait&gt;
      &lt;Species&gt;  
      &lt;Species&gt;
      &lt;Type&gt;Amphibian&lt;Type&gt;
      &lt;Name&gt;Snake&lt;Name&gt;
      &lt;Trait&gt;Long&lt;Trait&gt;
      &lt;Species&gt;
      &lt;Species&gt;
      &lt;Type&gt;Amphibian&lt;Type&gt;
      &lt;Name&gt;Snake&lt;Name&gt;
      &lt;Trait&gt;Sharp&lt;Trait&gt;
      &lt;Species&gt;
  </code></pre>
  
  <p>Notice that Snake and Rabbit shows up twice in the output</p>