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.
