Working with XML files and APEX - Part 3: Detail elements in a row with OUTER JOIN

After I described how to successfully import XML files into an APEX application and gave some basic information about the analyzing of xml data.
It's time for another example. This time we want to get all detail elements of a master element in one row.
Example XML data:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>  
<data>
 <stadium> 
   <team name="SG Dynamo Dresden" league="3. Liga"/>
   <event name="Konzert"/>
   <event name="Stadionführung"/>
 </stadium> 
 <stadium> 
   <team name="RB Leipzig" league="2. Liga"/>
   <event name="Konzert 1"/>
   <event name="Konzert 2"/>
   <event name="Konzert 3"/>
 </stadium> 
</data>

As you see we have two detail entries for element <stadium>. What I want are two rows including all the data of element <stadium>. Of course this means taking off some flexibility from my selection. Because I need to know what data I will have to deal with.
In my case I assume maximum one team and three events per stadium element.

The magic sql statement:

-- table: IMP_FM
-- XMLType column: XML_FILE
select
    t1_team,
    t1_league,
    e1_event,
    e2_event,
    e3_event
from    IMP_FM nx,
        XMLTable('/data/stadium' passing nx.xml_file
                 columns   
                    "P_T1" XMLTYPE PATH 'team[1]',  
                    "P_E1" XMLTYPE PATH 'event[1]',   
                    "P_E2" XMLTYPE PATH 'event[2]',  
                    "P_E3" XMLTYPE PATH 'event[3]' 
                ) STAD,
        XMLTable('team' PASSING STAD.P_T1
                 columns   t1_team varchar2(200) path '@name',
                           t1_league varchar2(200) path '@league'
                ) (+) T1,
        XMLTable('event' PASSING STAD.P_E1
                 columns   e1_event varchar2(200) path '@name'
                ) (+) E1,
        XMLTable('event' PASSING STAD.P_E2
                 columns   e2_event varchar2(200) path '@name'
                ) (+) E2,
        XMLTable('event' PASSING STAD.P_E3
                 columns   e3_event varchar2(200) path '@name'
                ) (+) E3
where nx.id = 1

Inside the sql I first create a set of four detail xml columns per row:
columns
  "P_T1" XMLTYPE PATH 'team[1]',
  "P_E1" XMLTYPE PATH 'event[1]',
  "P_E2" XMLTYPE PATH 'event[2]',
  "P_E3" XMLTYPE PATH 'event[3]'

Next I select the data of each xml element via an OUTER JOIN.
XMLTable('team' PASSING STAD.P_T1
    columns t1_team varchar2(200) path '@name',
                     t1_league varchar2(200) path '@league'
) (+) T1,

Finally I only have to select the columns I want to see.
If I wouldn't use the OUTER JOIN syntax then no result would appear. Because Oracle handles the XMLTable integration as INNER JOIN.
As I told this example only works if you know the exact definition of your XML detail data elements.

Post Tags:

XML