Using XMLTABLE with PASSING and COLUMNS Clauses–Part 8

One of the benefits of using XMLTABLE is the ability to get selected columns from the XQuery in the output directly. This will be very handy in cases where you need not parse the XML constructed from XQuery, but rather pull the columns directly from XMLTABLE itself. Sounds very technical? Not really, if this is understood with a very simple example. This time, let us take an example of portable hard disk, add some more information on our tables, so that this can be well understood.

Create a table as shown below and insert rows into the table as given in the INSERT statements.

CREATE TABLE PORTABLE_DRIVES ( ID NUMBER, DRIVE_DETAILS XMLTYPE ) INSERT INTO PORTABLE_DRIVES VALUES (1, '<DETAILS><PRODUCT>WD Elements SE 2.5" 500GB HD Portable USB Drive</PRODUCT><CAPACITY>500 GB</CAPACITY><PRICE>79</PRICE><DISCOUNT>10</DISCOUNT><RATING>4</RATING></DETAILS>') / INSERT INTO PORTABLE_DRIVES VALUES (2,'<DETAILS><PRODUCT>WD Elements SE 750GB Portable Hard Drive</PRODUCT><CAPACITY>750 GB</CAPACITY><PRICE>99</PRICE><DISCOUNT>8</DISCOUNT><RATING>4.5</RATING></DETAILS>') / INSERT INTO PORTABLE_DRIVES VALUES (3,'<DETAILS><PRODUCT>SEAGATE Expansion 2TB Desktop Hard Drive </PRODUCT><CAPACITY>2 TB</CAPACITY><PRICE>139</PRICE><DISCOUNT>12</DISCOUNT><RATING>4</RATING></DETAILS>') / INSERT INTO PORTABLE_DRIVES VALUES (4,'<DETAILS><PRODUCT>SEAGATE GoFlex 500GB Portable Black </PRODUCT><CAPACITY>500 GB</CAPACITY><PRICE>99</PRICE><DISCOUNT>6</DISCOUNT><RATING>3.5</RATING></DETAILS>') / INSERT INTO PORTABLE_DRIVES VALUES (5,'<DETAILS><PRODUCT>Iomega Select 34966 1TB External Desktop HD</PRODUCT><CAPACITY>1 TB</CAPACITY><PRICE>70</PRICE><DISCOUNT>5</DISCOUNT><RATING>3.5</RATING></DETAILS>')

The objective for us would be to display only the following in output; PRODUCT, CAPACITY, Actual Price and PRICE (after Discount) using XMLTABLE. If you recollect our earlier posts on XMLSEQUENCE, it should be very easy to do. Yet, we will try doing this with XMLTABLE and XQuery..To keep the learning alive, a XMLSEQUENCE solution will also be provided at the end for the same approach. We have seen lot of examples for XMLTABLE and XQuery, so I will give the direct SQL for this with the notes below

SELECT portable_drives.Product, /* These four are the required output variables, taken from the COLUMNS construct of XMLTABLE */        portable_drives.Capacity,        portable_drives.ActualPrice,        portable_drives.Price   FROM PORTABLE_DRIVES,        XMLTABLE('for $i in /DETAILS /* XQuery reads the DETAILS tags, and takes the DISCOUNT on discount variable */ let $discount:= $i/DISCOUNT return <OUTPUT> /* Output is constructed in return part */        <PRODUCT>{$i/PRODUCT}</PRODUCT>        <CAPACITY>{$i/CAPACITY}</CAPACITY>        <ORIGINAL>{$i/PRICE}</ORIGINAL>        <PRICE>{$i/PRICE - $discount }</PRICE> /* Price after discount calculated using subtraction */        </OUTPUT>'        PASSING DRIVE_DETAILS  /* XMLTYPE column sent in for reading */        COLUMNS  /* Only column details are sent, referring to return XML’s PATH, and not the entire XML, this is the Key difference */                  Product  VARCHAR2(100) PATH 'PRODUCT',                  Capacity VARCHAR2(20) PATH 'CAPACITY',                  ActualPrice NUMBER PATH 'ORIGINAL',                  Price NUMBER PATH 'PRICE') portable_drives

The output of this SQL is shown below

PRODUCT	CAPACITY	ACTUALPRICE	PRICE
WD Elements SE 2.5" 500GB HD Portable USB Drive	500 GB	79	69
WD Elements SE 750GB Portable Hard Drive	750 GB	99	91
SEAGATE Expansion 2TB Desktop Hard Drive	2 TB	139	127
SEAGATE GoFlex 500GB Portable Black	500 GB	99	93
Iomega Select 34966 1TB External Desktop HD	1 TB	70	65

This is exactly what we wanted.. In the next part of this post we will answer the following:

1) How to achieve the same output with XMLSequence?
2) How to achieve the output with XMLTABLE but no XQuery?
3) How to achieve this output with XMLTABLE and XQuery, and treating the discount as a percentage and not whole value.
4) How to get the same output without XMLSequence, XMLTABLE but only with EXTRACT and EXTRACTVALUE functions.

If you know the output to any of these 4, post your answers. Or, check out our part 9 of this series..coming up shortly.