In this post, we will explain how to use XMLTABLE construct on a XMLTYPE column to pull data from a table in Oracle. We had earlier done this using XMLQuery and we will be using the same LED TV example that we used in the previous parts. So, make sure that you grab all the table details and insert statements before we move on.I’m giving them below in case you cannot find it in the blog elsewhere;
CREATE TABLE LEDTVS ( TVID NUMBER, TVDESCR XMLTYPE ) INSERT INTO LEDTVS VALUES ('1',XMLTYPE('<TV><BRAND>Toshiba</BRAND><MODEL>26SL400</MODEL><TYPE>LED HDTV</TYPE><PRICE>300</PRICE><SIZE>26</SIZE></TV>')) INSERT INTO LEDTVS VALUES ('2',XMLTYPE('<TV><BRAND>Sony</BRAND><MODEL>KDL32BX300</MODEL><TYPE>LCD HDTV</TYPE><PRICE>400</PRICE><SIZE>32</SIZE></TV>')) INSERT INTO LEDTVS VALUES ('3',XMLTYPE('<TV><BRAND>Samsung</BRAND><MODEL>PN51D450</MODEL><TYPE>Plasma HDTV</TYPE><PRICE>720</PRICE><SIZE>51</SIZE></TV>')) INSERT INTO LEDTVS VALUES ('4',XMLTYPE('<TV><BRAND>Toshiba</BRAND><MODEL>55HT1U</MODEL><TYPE>LCD HDTV</TYPE><PRICE>999</PRICE><SIZE>55</SIZE></TV>')) INSERT INTO LEDTVS VALUES ('5',XMLTYPE('<TV><BRAND>LG</BRAND><MODEL>55LE7300</MODEL><TYPE>LED HDTV</TYPE><PRICE>1400</PRICE><SIZE>55</SIZE></TV>')) INSERT INTO LEDTVS VALUES ('6',XMLTYPE('<TV><BRAND>Sony</BRAND><MODEL>KDL46NX810</MODEL><TYPE>LED HDTV</TYPE><PRICE>1800</PRICE><SIZE>46</SIZE></TV>')) |
Our objective would be to pull all Toshiba BRAND TVs, with the following information TV Brand, Model, SIZE and Type. The Size should have a IF condition and needs to return BIG if the SIZE is greater than 40 else SMALL. Doing with XMLTABLE is almost same as XMLQuery, and the SQL to achieve this is provided below
SELECT TVDETAILS.COLUMN_VALUE FROM LEDTVS, XMLTABLE('for $i in /TV where $i/BRAND eq "Toshiba" return <TVSPECS> <TV Brand="{$i/BRAND}"/> <TV MODEL="{$i/MODEL}"/> <TV SIZE="{if ($i/SIZE > "40") then "BIG" else "SMALL"}"/> <TV TYPE="{$i/TYPE}"/> </TVSPECS>' PASSING TVDESCR)TVDETAILS |
If you see the SQL above, in the PASSING construct we are sending “TVDESCR” the XMLTYPE column to XMLTABLE, apply XQuery to it which filters all TVs where BRAND equals Toshiba, constructs the return XML and sends the XML back in TVDETAILS.COLUMN_VALUE. We discussed about COLUMN_VALUE in one of our blog posts earlier. This Query would return two rows for us, and the output is shown below
| <TVSPECS><TV Brand="Toshiba"></TV><TV MODEL="26SL400"></TV><TV SIZE="SMALL"></TV><TV TYPE="LED HDTV"></TV></TVSPECS> |
| <TVSPECS><TV Brand="Toshiba"></TV><TV MODEL="55HT1U"></TV><TV SIZE="BIG"></TV><TV TYPE="LCD HDTV"></TV></TVSPECS> |
It is very important to pass the table LEDTVS as the first entry in the FROM clause as XMLTABLE function uses the column of LEDTVS table.If you try changing the order and put the table name after the XMLTABLE function, you will get an “Invalid Identifier” error message on the screen against the TVDESCR column.The XMLTABLE function depends on the column from LEDTVS table for its processing.
XQuery supports if …else if clause also. If the size is greater than 30 we want to declare MEDIUM and SMALL otherwise, the SQL needs to be changed as shown below
SELECT TVDETAILS.COLUMN_VALUE FROM LEDTVS,XMLTABLE('for $i in /TV return <TVSPECS> <TV Brand="{$i/BRAND}"/> <TV MODEL="{$i/MODEL}"/> <TV SIZE="{if ($i/SIZE > "40") then "BIG" else if ($i/SIZE > "30") then "MEDIUM" else "SMALL"}"/> <TV TYPE="{$i/TYPE}"/> </TVSPECS>' PASSING TVDESCR)TVDETAILS |
For the Sony MODEL KDL32BX300 , this returns an output as shown below (satisfying the else if $i/SIZE > “30” criteria..The SQL above returns all the rows from LEDTVS table, as there is no “where” clause, but only the row corresponding to Sony Brand is shown below to illustrate the “else if” usage.
| <TVSPECS><TV Brand="Sony"></TV><TV MODEL="KDL32BX300"></TV><TV SIZE="MEDIUM"></TV><TV TYPE="LCD HDTV"></TV></TVSPECS> |
More discussion on XMLQuery will follow in the upcoming parts.
No comments:
Post a Comment