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..
No comments:
Post a Comment