Oracle XMLTable XQuery Example Part 3

In this post, we will explain the extended features of XMLTable by introducing XQuery. For basic XQuery and XMLTABLE examples, you may wish to refer to the related post section at the bottom of this post.

We have seen XMLTABLE and XMLSequence. Is XMLTABLE just a replacement for XMLSequence? I feel no, as XMLTABLE has got more powerful features over XMLSequence, predominant among them is to use XQuery. Let us get started with that with an simple example through this post.Let us use the same “Notebook” XML, but this time to explain this, we add a “Price” element to each of the Notebook models, The sample XML used becomes

<Product Type="Laptop">             <Notebook Brand="HP" Model="Pavilion dv6-3132TX Notebook">                         <Harddisk>640 GB</Harddisk>                         <Processor>Intel Core i7</Processor>                         <Price>1200</Price>                         <RAM>4 GB</RAM>             </Notebook>             <Notebook Brand="HP" Model="HP Pavilion dv6-3032TX Notebook">                         <Harddisk>640 GB</Harddisk>                         <Processor>Intel Core i7</Processor>                         <Price>1250</Price>                         <RAM>6 GB</RAM>             </Notebook>             <Notebook Brand="Toshiba" Model="Satellite A660/07R 3D Notebook">                         <Harddisk>640 GB</Harddisk>                         <Processor>Intel Core i7</Processor>                         <Price>1300</Price>                         <RAM>4 GB</RAM>             </Notebook>             <Notebook Brand="Toshiba" Model="Satellite A660/15J Notebook">                         <Harddisk>640 GB</Harddisk>                         <Processor>Intel Core i5</Processor>                         <Price>1275</Price>                         <RAM>6 GB</RAM>             </Notebook> </Product>

The task on hand, give me a simple SQL to display only those Notebook models, the price of which is less than or equal to $1250. To achieve this, instead of passing the “Notebook” tag to XMLTABLE, we pass a XQuery expression that will filter based on price, the SQL is shown below

SELECT NOTEBOOKS.*   FROM PRODUCT_DETAILS PO,        XMLTable('for $i in /Product/Notebook =>Select every structure where price is less than or equal to 1250                  where $i/Price <= 1250                  return $i' PASSING PO.NOTEBOOK COLUMNS                 "Seqno" FOR ORDINALITY,                 "BrandType" CHAR(10) PATH '@Brand' DEFAULT 'Sony',                 "ProductModel" CHAR(50) PATH '@Model',                 "Harddisk" CHAR(10) PATH 'Harddisk',                 "Price" CHAR(10) PATH 'Price',                 "Processor" CHAR(20) PATH 'Processor',                 "RAM" CHAR(10) PATH 'RAM') AS NOTEBOOKS

Basically, the SQL shown above instructs only those “Notebook” structures, the price of which is less than or equal to $1250. As expected, the output is shown below

Seqno	BrandType	ProductModel	Harddisk	Price	Processor	RAM
1	HP	Pavilion dv6-3132TX Notebook	640 GB	1200	Intel Core i7	4 GB
2	HP	HP Pavilion dv6-3032TX Notebook	640 GB	1250	Intel Core i7	6 GB

The same objective can be achieved using XMLSequence as well, the Query for which is provided below

SELECT * FROM ( select extractvalue(value(b), '/Notebook/@Brand') as "BrandType",        extractvalue(value(b), '/Notebook/@Model') as "ProductModel",        extractvalue(value(b), '/Notebook/Harddisk') as "Harddisk",        extractvalue(value(b), '/Notebook/Processor') as "Processor",        extractvalue(value(b), '/Notebook/RAM') as "RAM",        extractvalue(value(b), '/Notebook/Price') as PRICE   from product_details a,        table(xmlsequence(extract(a.notebook, '//Notebook'))) b ) WHERE PRICE <=1250

However, XQuery is more powerful approach, the usage of which we will illustrate in upcoming posts..