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
This comment has been removed by the author.
ReplyDeleteHi Jason,
ReplyDeleteGood 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.
Hi Jason,
ReplyDeleteGood 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.