XMLSequence SQL Example - Part 2

We are attempting to provide a solution for a querying a complex XML as provided in our example earlier, and come up with an approach that explains the ease of using Oracle XML functions.

We start with creating a table with single XMLTYPE field as shown below

create table prod_xml_Tab (demo_field xmltype)

Grab the XML that we are going to use from the question page, and insert it into this table. As on the lines of our earlier posts, we will follow an incremental approach to come with the complete solution, as many readers have commented that it is easy to follow rather than coming up with one big XML. So, our report needs to contain the following columns

 

Brand Vs Model Vs HardDisk Vs Processor Vs RAM Vs Price Vs Display Type Vs Weight Vs MonitorSize

Let us start of by getting Brand and Model. These are attributes of a repeating “Notebook” tag.

select extractvalue(value(a),'Notebook/@Brand') As Brand, extractvalue(value(a),'Notebook/@Model') As Model from prod_xml_tab b, table(xmlsequence(extract(demo_field,'Product/Notebook'))) a => All Notebook tags are pulled into a table here.

The SQL above creates a XMLTABLE of “NoteBook” tags and reads the Brand and Model attributes using extractvalue on the column of the XMLTABLE. The output of this SQL is shown below (note that we have got two columns for our report now) Brand Model -------------- HP    Pavilion dv6-3132TX Notebook HP    HP Pavilion dv6-3032TX Notebook HP    Pavilion dv6-3079TX Notebook Toshiba    Satellite A660/07R 3D Notebook Toshiba    Satellite A660/15J Notebook

Note that this example requires only one XMLTABLE to be created as we have all other report columns as tags under the “Notebook” tag. This means, fetching the values of other report columns is going to be a cakewalk for us..

select extractvalue(value(a),'Notebook/@Brand') As Brand, extractvalue(value(a),'Notebook/@Model') As Model, extractvalue(value(a),'Notebook/Harddisk') As Harddisk, extractvalue(value(a),'Notebook/Processor') As Processor, extractvalue(value(a),'Notebook/RAM') As RAM, extractvalue(value(a),'Notebook/Price') As Price, extractvalue(value(a),'Notebook/Display/@Type') As DisplayType, extractvalue(value(a),'Notebook/Display/@MonitorSize') As MonitorSize, extractvalue(value(a),'Notebook/Weight') As Weight from prod_xml_tab b, table(xmlsequence(extract(demo_field,'Product/Notebook'))) a

We read all other related information directly from the XMLTABLE created earlier. “Type” and “MonitorSize” are defined as attributes for “Display” and they are read accordingly..(on the same lines for Brand and Model). The output of this SQL matches the final report we were after. The report is provided below

Brand	Model	Hard Disk	Processor	RAM	Price	DisplayType	MonitorSize	Weight
HP	Pavilion dv6-3132TX Notebook	640 GB	Intel Core i7	4 GB	1189	LED	15.6	4.14
HP	HP Pavilion dv6-3032TX Notebook	640 GB	Intel Core i7	6 GB	1104	LED	15.6	4.1
HP	Pavilion dv6-3079TX Notebook	500 GB	Intel Core i7	4 GB	1099	LED	15.6	4.14
Toshiba	Satellite A660/07R 3D Notebook	640 GB	Intel Core i7	4 GB	1444	LED	15.6	4.88
Toshiba	Satellite A660/15J Notebook	640 GB	Intel Core i5	6 GB	1399	LED	15.6	3.9