Working with XML files and APEX - Part 2: Selecting data from XMLType column with XMLTable

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

Post Tags:

APEX examples XML