Tuesday, August 31, 2010

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.

No comments:

Post a Comment