Oracle XMLTABLE Example Part 1

In this post, we will explain the usage of XMLTABLE construct in Oracle SQL with a simple example. As we move on this series, we will present some more challenging examples for XMLTABLE.This is second in our series after our early attempt to explain XMLSequence. To make sure that we remember XMLSequence as well, we will also see how to achieve the objectives in these posts with XMLSequence as well.

The XML that we will be using for this example is provided below

<?xml version="1.0" encoding="UTF-8"?> <Product Type='Laptop'> <Notebook Brand="HP" Model="Pavilion dv6-3132TX Notebook"> <Harddisk>640 GB</Harddisk> <Processor>Intel Core i7</Processor> <RAM>4 GB</RAM> </Notebook> <Notebook Brand="HP" Model="HP Pavilion dv6-3032TX Notebook"> <Harddisk>640 GB</Harddisk> <Processor>Intel Core i7</Processor> <RAM>6 GB</RAM> </Notebook> <Notebook Brand="Toshiba" Model="Satellite A660/07R 3D Notebook"> <Harddisk>640 GB</Harddisk> <Processor>Intel Core i7</Processor> <RAM>4 GB</RAM> </Notebook> <Notebook Brand="Toshiba" Model="Satellite A660/15J Notebook"> <Harddisk>640 GB</Harddisk> <Processor>Intel Core i5</Processor> <RAM>6 GB</RAM> </Notebook> </Product>

The create table statement is given below

CREATE  TABLE PRODUCT_DETAILS (NOTEBOOK XMLTYPE)

The output we are expecting is

HP            Pavilion dv6-3132TX Notebook                        640 GB        Intel Core i7           4 GB      HP            HP Pavilion dv6-3032TX Notebook                   640 GB        Intel Core i7           6 GB      Toshiba     Satellite A660/07R 3D Notebook                     640 GB        Intel Core i7           4 GB      Toshiba     Satellite A660/15J Notebook                           640 GB        Intel Core i5           6 GB

We know how to do this with XMLSequence from our previous posts..To brush up, it is given below

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"   from product_details a,        table(xmlsequence(extract(a.notebook, '//Notebook'))) b

The same can be achieved through XMLTABLE function in Oracle. An example of how to get the same output using XMLTABLE is provided below

SELECT NOTEBOOKS.*   FROM PRODUCT_DETAILS PO,        XMLTable('//Notebook' PASSING PO.NOTEBOOK        COLUMNS  "BrandType"    CHAR(10) PATH '@Brand',                 "ProductModel" CHAR(50) PATH '@Model',                 "Harddisk" CHAR(10) PATH 'Harddisk',                 "Processor" CHAR(20) PATH 'Processor',                 "RAM" CHAR(10) PATH 'RAM') AS NOTEBOOKS

If you run these two examples, they will produce identical output..That makes us to ask quite a few questions;

1) How does this setup work? What is the difference between XMLTYPE and XMLSequence?
2) Is it advantageous to use XMLTYPE or XMLSequence? What extra benefits XMLTABLE offers?
3) Can you illustrate all the examples made with XMLSequence earlier, with XMLTABLE also? and even more..

We will address all this and even more in our next part of this post..Share your thoughts on this..