Oracle XQuery on XMLTYPE column Part 5

This is part 5 of our series on XMLQuery and in this post we will see how we can use XMLQuery function involving XQuery on XMLTYPE column. To run the example we will be demonstrating on this post, we will construct a simple table with two columns, one of them being of type XMLTYPE. We will add some data into this table to get ahead with the example.

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>'))

So, the objective on hand is to list all the TV models of size greater than 50 inches, in the following format

…..<TVSPECS>           <TV Brand="Samsung"/>           <TV MODEL="PN51D450"/>           <TV TYPE="Plasma HDTV"/> </TVSPECS>…………..

This would mean that the XQuery in the XMLQuery would have to read the XMLTYPE column and filter based on the “SIZE” tag. The SQL is provided below

SELECT * FROM ( SELECT TVID,        XMLQuery('for $i in /TV /*Read all the TV tags in XMLTYPE column*/           where  $i/SIZE > 50  /* filter for size greater than 50 inches */           return <TVSPECS> /* Construct output XML */                    <TV Brand="{$i/BRAND}"/>                    <TV MODEL="{$i/MODEL}"/>                    <TV TYPE="{$i/TYPE}"/>                  </TVSPECS>'          PASSING TVDESCR RETURNING CONTENT).getstringval() AS "TVDETAILS" /* Pass XMLTYPE TVDESCR column as input */ FROM LEDTVS) WHERE TVDETAILS IS NOT NULL

The explanation for the SQL is added along with the Query. As expected the output is shown below

3    <TVSPECS><TV Brand="Samsung"></TV><TV MODEL="PN51D450"></TV><TV TYPE="Plasma HDTV"></TV></TVSPECS> 4    <TVSPECS><TV Brand="Toshiba"></TV><TV MODEL="55HT1U"></TV><TV TYPE="LCD HDTV"></TV></TVSPECS> 5    <TVSPECS><TV Brand="LG"></TV><TV MODEL="55LE7300"></TV><TV TYPE="LED HDTV"></TV></TVSPECS>

The SQL returns all the rows from the LEDTVS table, however it leaves the TVDETAILS fetch NULL for rows that does not satisfy the “SIZE” criteria. We have an outer select to eliminate the NULL columns.

In the upcoming parts, we will see how to use more XQuery conditions inside the XMLQuery..