Wednesday, August 25, 2010

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

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi Jason,

    Good morning. I would really appreciate your help to solve to get the values from the following XML Document.


    - 0 CN001 0 2014-01-29T00:00:00-06:00 2014-01-29T00:00:00-06:00 1766 0 0 0 0 0 - - - - CN001 BBS01 111 88 15 8 0.0720720720720721 BEER - BBS01 - CN001 BBS02 BEER - BBS02 - CN001 BBS01 11 12906 SOFT-GOODS SOFT DRINK Full Throttle Coca Cola 6 11 1 16 - CN001 BBS01 10 2188 BEER DOMESTIC Lone Star Regular 6 2 1 3 4 - CN001 BBS02 11 12906 SOFT-GOODS SOFT DRINK Full Throttle Coca Cola - CN001 BBS02 10 2188 BEER DOMESTIC Lone Star Regular - - BBS01 BEER - BBS01 - BBS02 BEER - BBS02 - BBS01 11 12906 SOFT-GOODS SOFT DRINK Full Throttle Coca Cola - BBS01 10 2188 BEER DOMESTIC Lone Star Regular - BBS02 11 12906 SOFT-GOODS SOFT DRINK Full Throttle Coca Cola - BBS02 10 2188 BEER DOMESTIC Lone Star Regular

    Thanks in advance.

    ReplyDelete
  3. Hi Jason,

    Good morning. For some reason, I can not include the XML document with XML tag.

    Please let me know how to upload the XML Document for your perusal.

    Thanks in advance.

    ReplyDelete