After I described how to
successfully import XML files into an APEX application. It's time to start to analyze them.
I still assume that this is the example XML file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<leagues>
<league id="1" name="2. Bundeliga">
<teams>
<team>
<id>1</id>
<name location="Dresden" stadium="Glücksgas-Stadion">SG Dynamo Dresden</name>
</team>
<team>
<id>2</id>
<name location="Cologne" stadium="RheinEnergieStadion">1. FC Köln</name>
</team>
<team>
<id>3</id>
<name location="Berlin" stadium="Alte Försterei">1. FC Union Berlin</name>
</team>
<team>
<id>4</id>
<name location="Düsseldorf" stadium="Esprit Arena">Fortuna Düsseldorf</name>
</team>
</teams>
</league>
</leagues>
The first select will find all team names and locations:
-- table: IMP_FM
-- XMLType column: XML_FILE
SELECT T.TEAM,
T.LOCATION
FROM
IMP_FM FM,
XMLTable('/leagues/league/teams/team/name' PASSING FM.XML_FILE
COLUMNS "TEAM" VARCHAR2(255) PATH 'text()',
"LOCATION" VARCHAR2(255) PATH '@location'
) T
What am I doing here?
In the FROM-clause I define the XML table "IMP_FM" and right after that I generate a new XMLTable object.
Inside this object I define the start entry "/leagues/league/teams/team/name" from where I want to select the data.
In the PASSING-clause I define the XMLType column from where the data is coming.
Next step is to set the SQL columns based on the XML data.
To get the team names I need to select the text from the xml element "name". To do that I use the function "text()".
To select the attribute "location" I need to use an "@" in front of the attribute name.
This newly created table will have the name "T" and can be selected in the SELECT-clause.
In the next example I will select league and team names. Actually they would be two tables with an 1-n relationship.
Because of that I have to implement two XMLTable objects in the select statement.
SELECT L.LEAGUE,
T.TEAM,
T.LOCATION
FROM
IMP_FM FM,
XMLTable('/leagues/league' PASSING FM.XML_FILE
COLUMNS "LEAGUE" VARCHAR2(255) PATH '@name',
"T_XML" XMLTYPE PATH 'teams/team'
) L,
XMLTable('team/name' PASSING L.T_XML
COLUMNS "TEAM" VARCHAR2(255) PATH 'text()',
"LOCATION" VARCHAR2(255) PATH '@location'
) T
What am I doing here?
In the first XMLTable object I generate a XMLType column with the data from path "teams/team". This column is named as "T_XML". The generated table is named as "L".
Now I create a new XMLTable object based on the data of the XMLType column "L.T_XML". I follow the same logic as in first select and just use a shorter PATH "team/name".
More information about XML sub-selects can be found in this blog post:
New 12c XMLTABLE’s “RETURNING SEQUENCE BY REF” clause
In the last example I want to show how to work with XML namespaces. For that our XML changes a bit.
<?xml version="1.0" encoding="UTF-8"?>
<leagues xmlns:tt="http://www.footballleagues_xml.org/schemas/team" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.footballleagues_xml.org/schemas http://www.footballleagues_xml.org/schemas/xml/XML.xsd" version="2.1" xmlns="http://www.footballleagues_xml.org/schemas">
<league id="1" name="2. Bundeliga">
<teams>
<team>
<tt:id>1</tt:id>
<tt:name location="Dresden" stadium="Glücksgas-Stadion">SG Dynamo Dresden</tt:name>
</team>
<team>
<tt:id>2</tt:id>
<tt:name location="Cologne" stadium="RheinEnergieStadion">1. FC Köln</tt:name>
</team>
<team>
<tt:id>3</tt:id>
<tt:name location="Berlin" stadium="Alte Försterei">1. FC Union Berlin</tt:name>
</team>
<team>
<tt:id>4</tt:id>
<tt:name location="Düsseldorf" stadium="Esprit Arena">Fortuna Düsseldorf</tt:name>
</team>
</teams>
</league>
</leagues>
To select the data now is a bit more complicated:
SELECT T.TEAM,
T.LOCATION
FROM
IMP_FM FM,
XMLTable(XMLNameSpaces('http://www.footballleagues_xml.org/schemas/team' as "tt",
default 'http://www.footballleagues_xml.org/schemas'),
'/leagues/league/teams/team' PASSING FM.XML_FILE
COLUMNS "TEAM" VARCHAR2(255) PATH 'tt:name/text()',
"LOCATION" VARCHAR2(255) PATH 'tt:name/@location'
) T
What am I doing here?
Inside the XMLTable object I define a "XMLNameSpace" which is marked as "tt".
Inside the column definition I use "tt:" to select the data.
More information about multiple Namespaces can be found here:
https://forums.oracle.com/thread/2381998
That's it for today. Cheers Tobias
