XML Parsing with Default Namespaces via XMLTable

In my previous post XML Parsing with Namespaces via XMLTable I covered parsing of XML where a namespace prefix existed for each namespace. What happens when you have to parse XML where some of the XML is in a default namespace? With XMLTable, it is simple as the XMLNamespace clause has a DEFAULT option to deal with this situation. Here is another round of simple example based off the ongoing example.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

SQL> CREATE TABLE your_table (xml_col   XMLTYPE);

Table created

SQL>
SQL> INSERT INTO your_table
2  VALUES
3   ('<employees xmlns="abc.com/123" b="xyz.net/456">
4   <emp>
5    <name>Scott</name>
6    <b:favorites>
7     <b:color>red</b:color>
8     <b:color>orange</b:color>
9    </b:favorites>
10   </emp>
11   <emp>
12    <name>John</name>
13    <b:favorites>
14     <b:color>blue</b:color>
15     <b:color>green</b:color>
16    </b:favorites>
17   </emp>
18  </employees>');

1 row inserted

SQL> commit;

Commit complete

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

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

NAM
--------------------
Scott
John
SQL> --
SQL> -- Method 4
SQL> -- declares a prefix for the default
SQL> SELECT xt.nam
2    FROM your_table yt,
3         XMLTable(XMLNamespaces('abc.com/123' AS "a"),
4                  'a:employees/a:emp[2]'
5                  PASSING yt.xml_col
6                  COLUMNS
7                  nam  VARCHAR2(20) PATH 'a:name') xt;

NAM
--------------------
John
SQL> --
SQL> -- Method 5.a
SQL> SELECT xt.color
2    FROM your_table yt,
3         XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
4                                'xyz.net/456' AS "b"),
5                  'employees/emp/b:favorites/b:color'
6                  PASSING yt.xml_col
7                  COLUMNS
8                  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(XMLNamespaces(DEFAULT 'abc.com/123',
4                                'xyz.net/456' AS "b"),
5                  'employees/emp'
6                  PASSING yt.xml_col
7                  COLUMNS
8                  nam      VARCHAR2(20) PATH 'name',
9                  color_t  XMLTYPE      PATH 'b:favorites') xt,  -- path to the node that repeats
10         XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
11                  'b:favorites/b:color'
12                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
13                  COLUMNS
14                  color  VARCHAR2(10) PATH '.') xt2;

SELECT xt.nam, xt2.color
FROM your_table yt,
    XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
                           'xyz.net/456' AS "b"),
             'employees/emp'
             PASSING yt.xml_col
             COLUMNS
             nam      VARCHAR2(20) PATH 'name',
             color_t  XMLTYPE      PATH 'b:favorites') xt,  -- path to the node that repeats
    XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
             'b:favorites/b:color'
             PASSING xt.color_t  -- define input XMLType as output of above, aka a join
             COLUMNS
             color  VARCHAR2(10) PATH '.') xt2

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '/oraxq_defpfx:emp/b:favorites'
SQL> --
SQL> -- Method 5.c
SQL> SELECT xt.nam, xt2.color
2    FROM your_table yt,
3         XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
4                                'xyz.net/456' AS "b"),
5                  'employees/emp'
6                  PASSING yt.xml_col
7                  COLUMNS
8                  nam      VARCHAR2(20) PATH 'name',
9                  color_t  XMLTYPE      PATH 'b:favorites/b:color') xt,  -- path to the node that repeats
10         XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
11                  '/b:color'
12                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
13                  COLUMNS
14                  color  VARCHAR2(10) PATH '.') xt2;

SELECT xt.nam, xt2.color
FROM your_table yt,
    XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
                           'xyz.net/456' AS "b"),
             'employees/emp'
             PASSING yt.xml_col
             COLUMNS
             nam      VARCHAR2(20) PATH 'name',
             color_t  XMLTYPE      PATH 'b:favorites/b:color') xt,  -- path to the node that repeats
    XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
             '/b:color'
             PASSING xt.color_t  -- define input XMLType as output of above, aka a join
             COLUMNS
             color  VARCHAR2(10) PATH '.') xt2

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '/oraxq_defpfx:emp/b:favorites/b:color'

SQL> 

Wait, what? Errors? I'll get back to that.
In Method 3, we declared the default namespace for the XML via
DEFAULT 'abc.com/123'
On the XML elements that reside in the default namespace, we do not need to include a prefix as the default namespace is assumed. XMLTable makes it that simple. You can still declare the namespace and assign it a prefix as Method 4 shows if you prefer or would need in situations where the XML has many different default namespaces.

Now about Method 5.b and 5.c, where did that error come from? That would be a bug in the version of Oracle I am using. The SQL runs correctly on 10.2 and 11.2 at least. I can't speak for other versions of 11.1
What can we learn from this bug? We learn that when the DEFAULT namespace is declared within an XMLNamespaces clause that Oracle behind the scenes creates a prefix called oraxq_defpfx and applies it to the appropriate elements in the XQuery statement.

You can work around this bug by declaring your own prefix for the namespace as shown in Method 4 and using that prefix in the XPath accordingly. I would avoid declaring any namespace prefixes that start with "ora" just to be safe. Final note, since "oraxq_defpfx" is an internal definition for the default namespace prefix, that name is subject to be changed at any time by Oracle for any reason.

XML Parsing with Namespaces via XMLTable

In Basic XML Parsing via XMLTable I covered the basics for parsing simple XML stored in the database via SQL, but most XML has one or more namespaces associated with it. So how do you deal with that? With XMLTable it is simple. As the XMLTable documentation shows, there is an XMLnamespaces_clause where this information can be provided. This translates into the XMLNamespaces(), parameter within the XMLTable syntax. The documentation doesn't show how to use it, but the XML DB FAQ Thread on the OTN XML DB forum does (see the "How do I declare namespace prefix mapping with XMLTable()?" thread). As before in the PL/SQL example showing namespaces, I've taken the previous XML and added two namespaces to it and updated the appropriate calls to make this example.

The updated XML I used.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

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

Table created
SQL> INSERT INTO your_table
2  VALUES
3  ('<a:employees xmlns:a="abc.com/123" xmlns:b="xyz.net/456">
4   <a:emp>
5    <a:name>Scott</a:name>
6    <b:favorites>
7     <b:color>red</b:color>
8     <b:color>orange</b:color>
9    </b:favorites>
10   </a:emp>
11   <a:emp>
12    <a:name>John</a:name>
13    <b:favorites>
14     <b:color>blue</b:color>
15     <b:color>green</b:color>
16    </b:favorites>
17   </a:emp>
18  </a:employees>');

1 row inserted
SQL> commit;

Commit complete

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

NAM
--------------------
Scott
John
SQL> --
SQL> -- Method 4
SQL> SELECT xt.nam
 2    FROM your_table yt,
 3         XMLTable(XMLNamespaces('abc.com/123' AS "a"),
 4    'a:employees/a:emp[2]'
 5                  PASSING yt.xml_col
 6                  COLUMNS
 7                  nam  VARCHAR2(20) PATH 'a:name') xt;

NAM
--------------------
John
SQL> --
SQL> -- Method 5.a
SQL> SELECT xt.color
 2    FROM your_table yt,
 3         XMLTable(XMLNamespaces('abc.com/123' AS "a",
 4                                'xyz.net/456' AS "b"),
 5           'a:employees/a:emp/b:favorites/b:color'
 6                  PASSING yt.xml_col
 7                  COLUMNS
 8                  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(XMLNamespaces('abc.com/123' AS "a",
 4                                'xyz.net/456' AS "b"),
 5                  'a:employees/a:emp'
 6                  PASSING yt.xml_col
 7                  COLUMNS
 8                  nam      VARCHAR2(20) PATH 'a:name',
 9                  color_t  XMLTYPE      PATH 'b:favorites') xt,  -- path to the node that repeats
10         XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
11                  'b:favorites/b:color'
12                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
13                  COLUMNS
14                  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(XMLNamespaces('abc.com/123' AS "a",
 4                                'xyz.net/456' AS "b"),
 5                  'a:employees/a:emp'
 6                  PASSING yt.xml_col
 7                  COLUMNS
 8                  nam      VARCHAR2(20) PATH 'a:name',
 9                  color_t  XMLTYPE      PATH 'b:favorites/b:color') xt,  -- path to the node that repeats
10         XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
11                  '/b:color'
12                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
13                  COLUMNS
14                  color  VARCHAR2(10) PATH '.') xt2;

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

which is exactly the same output as the previous example. This is what we expected. As always, you only need to pass along namespaces that are referenced directly in the XMLTable and not all the namespaces associated with the XML.

Trouble shooting tips:

• Don't forgot to include the comma after the closing parenthesis on the XMLNamespaces(), structure otherwise you'll get ORA-02000: missing , keyword