Tags:

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

Von Tobias Arnhold 1.08.2015
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:

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

Kim Berg Hansen 08 January, 2015 16:02

Very nice and useful. I haven't tried outer joining an XMLTABLE - I've used some weird XQuery instead sometimes.

The ANSI join workaround:


select
t1_team,
t1_league,
e1_event,
e2_event,
e3_event
from IMP_FM nx
join
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
on p_t1 is not null
left outer join
XMLTable(
'team' PASSING STAD.P_T1
columns
t1_team varchar2(200) path '@name',
t1_league varchar2(200) path '@league'
) T1
on t1_team is not null
left outer join
XMLTable(
'event' PASSING STAD.P_E1
columns
e1_event varchar2(200) path '@name'
) E1
on e1_event is not null
left outer join
XMLTable(
'event' PASSING STAD.P_E2
columns
e2_event varchar2(200) path '@name'
) E2
on e2_event is not null
left outer join
XMLTable(
'event' PASSING STAD.P_E3
columns
e3_event varchar2(200) path '@name'
) E3
on e3_event is not null
where nx.id = 1
/


In 12c an alternative is using APPLY:


select
t1_team,
t1_league,
e1_event,
e2_event,
e3_event
from
(
(
(
IMP_FM nx
cross apply
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
outer apply
XMLTable(
'team' PASSING STAD.P_T1
columns
t1_team varchar2(200) path '@name',
t1_league varchar2(200) path '@league'
) T1
)
outer apply
XMLTable(
'event' PASSING STAD.P_E1
columns
e1_event varchar2(200) path '@name'
) E1
)
outer apply
XMLTable(
'event' PASSING STAD.P_E2
columns
e2_event varchar2(200) path '@name'
) E2
)
outer apply
XMLTable(
'event' PASSING STAD.P_E3
columns
e3_event varchar2(200) path '@name'
) E3
where nx.id = 1
/


For some reason grouping the apply's with parentheses is needed. Without parentheses I get ORA-00904: "STAD"."P_E2": invalid identifier:


select
t1_team,
t1_league,
e1_event,
e2_event,
e3_event
from IMP_FM nx
cross apply
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
outer apply
XMLTable(
'team' PASSING STAD.P_T1
columns
t1_team varchar2(200) path '@name',
t1_league varchar2(200) path '@league'
) T1
outer apply
XMLTable(
'event' PASSING STAD.P_E1
columns
e1_event varchar2(200) path '@name'
) E1
outer apply
XMLTable(
'event' PASSING STAD.P_E2
columns
e2_event varchar2(200) path '@name'
) E2
outer apply
XMLTable(
'event' PASSING STAD.P_E3
columns
e3_event varchar2(200) path '@name'
) E3
where nx.id = 1
/


Must be a reason for it, I suppose ;-)

Regards
Kim Berg Hansen

Marco Gralike 23 January, 2015 13:17

Overall your SQL/XML statement will not be optimal, performance wise. Don't forget everytime you pass the data via the passing clause or use XMLTYPE(), the content will be checked being XML that is "XML wellformed". This is CPU and memory intensive.

Have a look at
https://community.oracle.com/message/12787630

or

https://odieweblog.wordpress.com/2011/12/13/how-to-flatten-out-an-xml-hierarchical-structure/

How you can decrease the use of passing / xmltype calls.