sqlxmloracle-database

Oracle SQL Order By with XML and Union ALL

I wrote a select statement like this:

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;

This is my input

enter image description here

The query runs fine, but when I added an UNION ALL like so:

 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;

I got the error : ORA-00904: "Name": invalid identifier

How do I use Order By correctly in the above example?

My desired output is:

    <Species>
    <Type>Bird<Type>
    <Name>Eagle<Name>
    <Species>
    <Species>
    <Type>Mammal<Type>
    <Name>Fox<Name>
    <Trait>Sharp<Trait>
    <Species>
    <Species>
    <Type>Plant<Type>
    <Name>Lettuce<Name>
    <Species>
    <Species>
    <Type>Mammal<Type>
    <Name>Rabbit<Name>
    <Trait>Short<Trait>
    <Species>
    <Species>
    <Type>Mammal<Type>
    <Name>Rabbit<Name>
    <Trait>Dull<Trait>
    <Species>  
    <Species>
    <Type>Amphibian<Type>
    <Name>Snake<Name>
    <Trait>Long<Trait>
    <Species>
    <Species>
    <Type>Amphibian<Type>
    <Name>Snake<Name>
    <Trait>Sharp<Trait>
    <Species>

Notice that Snake and Rabbit shows up twice in the output

Solution

  • You can order the result of XMLAgg using the order_by_clause of XMLAgg.

    In the first step perform a simple UNION ALL(without XML) to duplicate the data as required.

    Finaly use the order_by_clause of XMLAgg

    example data

    create table Table_Animals as
select 'T1' type, 'dog' name, 'tai1' tail, 'Y' prey, 'tee1' teeth from dual union all
select 'T1' type, 'elephant' name, 'tai2' tail, 'Y' prey, 'tee2' teeth from dual union all
select 'T2' type, 'cat' name, 'tai3' tail, null prey, 'tee3' teeth from dual;

    The query

    with ua as (
select TYPE, NAME, TAIL, PREY, TEETH, Tail as trait from Table_Animals union all
select TYPE, NAME, TAIL, PREY, TEETH, Teeth  as trait from Table_Animals where Prey is not null)
Select 
 xmlagg(xmlelement( "Animal", 
                  xmlelement("Type",Type),
                  xmlelement("Name",Name),
                  xmlelement("Trait",Trait)) order by name) xml_col
From ua
;

    note the definition of the column trait in the subquery.

    note the order by name near the xml_col

    gives

    <Animal>
  <Type>T2
  </Type>
  <Name>cat
  </Name>
  <Trait>tai3
  </Trait>
</Animal>
<Animal>
  <Type>T1
  </Type>
  <Name>dog
  </Name>
  <Trait>tai1
  </Trait>
</Animal>
<Animal>
  <Type>T1
  </Type>
  <Name>dog
  </Name>
  <Trait>tee1
  </Trait>
</Animal>
<Animal>
  <Type>T1
  </Type>
  <Name>elephant
  </Name>
  <Trait>tai2
  </Trait>
</Animal>
<Animal>
  <Type>T1
  </Type>
  <Name>elephant
  </Name>
  <Trait>tee2
  </Trait>
</Animal>