Basic XML Parsing via XMLTable

In Basic XML Parsing via PL/SQL, I looked at "How do I parse this XML?" when the XML resides in PL/SQL. Oftentimes, the XML resides in the database and so Oracle provides another option for parsing the XML, the XMLTable command. This command became available with the release of 10.2. On versions <= 10.1, you have to use TABLE(xmlsequence(extract(...))). I might cover that at some point in the future but will see. While reading that documentation may make XMLTable seem complex, which it can be, it is very easy to use. Let us start with putting our XML into the DB via:

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

SQL>
SQL> CREATE TABLE your_table
 2  (xml_col   XMLTYPE);

Table created

SQL>
SQL> INSERT INTO your_table
 2  VALUES
 3  ('<employees>
 4   <emp>
 5    <name>Scott</name>
 6    <favorites>
 7     <color>red</color>
 8     <color>orange</color>
 9    </favorites>
10   </emp>
11   <emp>
12    <name>John</name>
13    <favorites>
14     <color>blue</color>
15     <color>green</color>
16    </favorites>
17   </emp>
18  </employees>');

1 row inserted

SQL> commit;

Commit complete

Now let's get out the same data we did in the original PL/SQL example. For this we will only be doing Methods 3 - 5 since they cover Methods 1 and 2 as well.

SQL> -- Method 3
SQL> SELECT xt.nam
 2    FROM your_table yt,
 3         XMLTable('employees/emp'
 4                  PASSING yt.xml_col  -- defines source of XMLType, can define a join
 5                  COLUMNS
 6                  nam  VARCHAR2(20) PATH 'name') xt;

NAM
--------------------
Scott
John

SQL> --
SQL> -- Method 4
SQL> SELECT xt.nam
 2    FROM your_table yt,
 3         XMLTable('employees/emp[2]'
 4                  PASSING yt.xml_col
 5                  COLUMNS
 6                  nam  VARCHAR2(20) PATH 'name') xt;

NAM
--------------------
John

SQL> --
SQL> -- Method 5.a
SQL> SELECT xt.color
 2    FROM your_table yt,
 3         XMLTable('employees/emp/favorites/color'
 4                  PASSING yt.xml_col
 5                  COLUMNS
 6                  color  VARCHAR2(10) PATH '.') xt;

COLOR
----------
red
orange
blue
green

SQL> --
SQL> -- Method 5.b
SQL> SELECT xt.nam, xt2.color
 2    FROM your_table yt,
 3         XMLTable('employees/emp'
 4                  PASSING yt.xml_col
 5                  COLUMNS
 6                  nam      VARCHAR2(20) PATH 'name',
 7                  color_t  XMLTYPE      PATH 'favorites') xt,  -- path to the node that repeats
 8         XMLTable('favorites/color'
 9                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
10                  COLUMNS
11                  color  VARCHAR2(10) PATH '.') xt2;

NAM                  COLOR
-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green

SQL> --
SQL> -- Method 5.c
SQL> SELECT xt.nam, xt2.color
 2    FROM your_table yt,
 3         XMLTable('employees/emp'
 4                  PASSING yt.xml_col
 5                  COLUMNS
 6                  nam      VARCHAR2(20) PATH 'name',
 7                  color_t  XMLTYPE      PATH 'favorites/color') xt,  -- path to the node that repeats
 8         XMLTable('/color'
 9                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
10                  COLUMNS
11                  color  VARCHAR2(10) PATH '.') xt2;

NAM                  COLOR
-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green

Methods 3 - 5.a here extract the same information as we did via PL/SQL.

Breaking down the Oracle documentation syntax, I will cover XMLnamespaces_clause in another post. The XQuery_string can be a simple XPath as shown above or more complex XQuery statements to be demonstrated later. The XML_passing_clause is just the PASSING line where the input XML is defined. This can join the XMLTable to another table/XMLTable, a variable, or be XML generated right there. The XML_table_column section following COLUMNS simply lists how to find the information referenced by the XQuery_string. The information is relative to the results of the XQuery_string.

Trouble shooting tips:

• If you are unsure of what the XMLType looks like at some point within an XMLTable, simply create a column of XMLType and include it in the SELECT list. In example 5.c, adding xt.color_t to the select list allows us to see what Oracle was passing into the second XMLTable.