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