Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
SQL>
SQL> CREATE TABLE your_table
2 (xml_col XMLTYPE);
Table created
SQL>
SQL> INSERT INTO your_table
2 VALUES
3 ('<employees>
4 <emp>
5 <name>Scott</name>
6 <favorites>
7 <color>red</color>
8 <color>orange</color>
9 </favorites>
10 </emp>
11 <emp>
12 <name>John</name>
13 <favorites>
14 <color>blue</color>
15 <color>green</color>
16 </favorites>
17 </emp>
18 </employees>');
1 row inserted
SQL> commit;
Commit complete
Now let's get out the same data we did in the original PL/SQL example. For this we will only be doing Methods 3 - 5 since they cover Methods 1 and 2 as well.
SQL> -- Method 3
SQL> SELECT xt.nam
2 FROM your_table yt,
3 XMLTable('employees/emp'
4 PASSING yt.xml_col -- defines source of XMLType, can define a join
5 COLUMNS
6 nam VARCHAR2(20) PATH 'name') xt;
NAM
--------------------
Scott
John
SQL> --
SQL> -- Method 4
SQL> SELECT xt.nam
2 FROM your_table yt,
3 XMLTable('employees/emp[2]'
4 PASSING yt.xml_col
5 COLUMNS
6 nam VARCHAR2(20) PATH 'name') xt;
NAM
--------------------
John
SQL> --
SQL> -- Method 5.a
SQL> SELECT xt.color
2 FROM your_table yt,
3 XMLTable('employees/emp/favorites/color'
4 PASSING yt.xml_col
5 COLUMNS
6 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('employees/emp'
4 PASSING yt.xml_col
5 COLUMNS
6 nam VARCHAR2(20) PATH 'name',
7 color_t XMLTYPE PATH 'favorites') xt, -- path to the node that repeats
8 XMLTable('favorites/color'
9 PASSING xt.color_t -- define input XMLType as output of above, aka a join
10 COLUMNS
11 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('employees/emp'
4 PASSING yt.xml_col
5 COLUMNS
6 nam VARCHAR2(20) PATH 'name',
7 color_t XMLTYPE PATH 'favorites/color') xt, -- path to the node that repeats
8 XMLTable('/color'
9 PASSING xt.color_t -- define input XMLType as output of above, aka a join
10 COLUMNS
11 color VARCHAR2(10) PATH '.') xt2;
NAM COLOR
-------------------- ----------
Scott red
Scott orange
John blue
John green
Methods 3 - 5.a here extract the same information as we did via PL/SQL.
Breaking down the Oracle documentation syntax, I will cover XMLnamespaces_clause in another post. The XQuery_string can be a simple XPath as shown above or more complex XQuery statements to be demonstrated later. The XML_passing_clause is just the PASSING line where the input XML is defined. This can join the XMLTable to another table/XMLTable, a variable, or be XML generated right there. The XML_table_column section following COLUMNS simply lists how to find the information referenced by the XQuery_string. The information is relative to the results of the XQuery_string.
Trouble shooting tips:
- If you are unsure of what the XMLType looks like at some point within an XMLTable, simply create a column of XMLType and include it in the SELECT list. In example 5.c, adding xt.color_t to the select list allows us to see what Oracle was passing into the second XMLTable.
It's awesome.. It works fine thanks
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteAim to extract the BRAND and its COST and it is getting extracted without any problem using
ReplyDeleteCREATE TABLE xml_tab (seqno number default 1, xml_data XMLType);
XMLTable.
INSERT INTO xml_tab
VALUES
(1, XMLType('
1000
2000
'));
SELECT t.seqno, tx.* FROM xml_tab t,
XMLTABLE('/PRODUCTS/LAPTOPS/NOTEBOOK' PASSING t.xml_data
COLUMNS brandname CHAR(20) PATH '@BRAND',
cost CHAR(20) PATH '.') tx
WHERE brandname in ('HP', 'LENOVA') AND seqno = 1;
Outpupt
SEQNO BRANDNAME COST
---------- -------------------- --------------------
1 HP 1000
1 LENOVA 2000
Below query will demonstrate the problem I am facing and need your kind help to resovle it.
INSERT INTO xml_tab
VALUES
(2, XMLType('
3000
1400
5000
'));
(Below query is not working)
SELECT t.seqno, tx.* FROM xml_tab t,
XMLTABLE('/PRODUCTS/LAPTOPS/NOTEBOOK[1]' PASSING t.xml_data
COLUMNS brandname CHAR(20) PATH '@BRAND',
cost CHAR(20) PATH '.') tx
WHERE brandname in ('HP', 'LENOVA') AND seqno = 2;
My requirement is to display only single row of HP and LENOVA brand even if there are multiple XML tags of one brand. Therefore, desired output is
SEQNO BRANDNAME COST
---------- -------------------- --------------------
2 HP 3000
2 LENOVA 5000
Kindly suggest.
Thanks in advance
Sandeep