XMLTABLE Function with XQuery on XMLTYPE Column–Part 9

Part 8  of our blog discussed about applying COLUMN clause to XMLTABLE function, that involved an XQuery. At the end of part 8, we had presented the user with four challenges for achieving the same output using different XML functions in Oracle. In this post, we will present the solution to each of the four approaches and compare the output. 

Approach 1 # How to get the output using EXTRACTVALUE function of Oracle? Example below..

SELECT EXTRACTVALUE(DRIVE_DETAILS, 'DETAILS/PRODUCT') AS PRODUCT,

       EXTRACTVALUE(DRIVE_DETAILS, 'DETAILS/CAPACITY') AS CAPACITY,

       EXTRACTVALUE(DRIVE_DETAILS, 'DETAILS/PRICE') AS ACTUALPRICE,

       EXTRACTVALUE(DRIVE_DETAILS, 'DETAILS/PRICE') -

       EXTRACTVALUE(DRIVE_DETAILS, 'DETAILS/DISCOUNT') AS PRICE

FROM PORTABLE_DRIVES

This approach is very straight forward. We use EXTRACTVALUE function to query the XMLTYPE column and pull the XML tags we are after. For calculating discount, we pull both PRICE and DISCOUNT and subtract them.

Approach 2 # How to get the same output using XMLSequence function of Oracle? Example below..

SELECT EXTRACTVALUE(VALUE(A), '/DETAILS/PRODUCT') AS PRODUCT,

       EXTRACTVALUE(VALUE(A), '/DETAILS/CAPACITY') AS CAPACITY,

       EXTRACTVALUE(VALUE(A), '/DETAILS/PRICE') AS ACTUALPRICE,

       EXTRACTVALUE(VALUE(A), '/DETAILS/PRICE') -

       EXTRACTVALUE(VALUE(A), '/DETAILS/DISCOUNT') AS PRICE

FROM PORTABLE_DRIVES,

TABLE(XMLSEQUENCE(EXTRACT(DRIVE_DETAILS, '/DETAILS'))) A

This approach is just a recap of what we discussed about XMLSequence earlier. We create an in-memory table using XMLSequence and then use Extractvalue on that to get the required columns in the output.

Approach 3 # How to get the same output with XMLTABLE and not using XQuery? Example below

SELECT portable_drives.Product,

       portable_drives.Capacity,

       portable_drives.ActualPrice,

       portable_drives.ActualPrice - portable_drives.Discount As Price

  FROM PORTABLE_DRIVES,

       XMLTABLE('//DETAILS' PASSING DRIVE_DETAILS COLUMNS Product

                VARCHAR2(100) PATH 'PRODUCT',

                Capacity VARCHAR2(20) PATH 'CAPACITY',

                ActualPrice NUMBER PATH 'PRICE',

                Discount NUMBER PATH 'DISCOUNT') portable_drives

Approach 4 #  Using XMLTABLE and XQuery..We already discussed this method in previous post, I’m providing the SQL below to summarize

SELECT portable_drives.Product,

       portable_drives.Capacity,

       portable_drives.ActualPrice,

       portable_drives.Price

  FROM PORTABLE_DRIVES,

       XMLTABLE('for $i in /DETAILS

let $discount:= $i/DISCOUNT

return <OUTPUT>

       <PRODUCT>{$i/PRODUCT}</PRODUCT>

       <CAPACITY>{$i/CAPACITY}</CAPACITY>

       <ORIGINAL>{$i/PRICE}</ORIGINAL>

       <PRICE>{$i/PRICE - $discount }</PRICE>

       </OUTPUT>'

       PASSING DRIVE_DETAILS 

       COLUMNS 

                 Product  VARCHAR2(100) PATH 'PRODUCT',

                 Capacity VARCHAR2(20) PATH 'CAPACITY',

                 ActualPrice NUMBER PATH 'ORIGINAL',

                 Price NUMBER PATH 'PRICE') portable_drives

All these four approaches provide the same output. The output is provided below

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

The decision to adopt a specific approach depends on the XML data on which the SQL is being used and the Oracle version on which the Query is being run.So, you need to be careful in adopting the right approach to the right situation.

Finally, we asked one more question..To treat division as a percentage rather than a whole value and run the same rules. The SQL for that example is given below

SELECT portable_drives.Product,

       portable_drives.Capacity,

       portable_drives.ActualPrice,

       portable_drives.Price

  FROM PORTABLE_DRIVES,

       XMLTABLE('for $i in /DETAILS

let $discount:= $i/PRICE * $i/DISCOUNT div 100 /* ‘div’ used to do division in XQuery */

return <OUTPUT>

       <PRODUCT>{$i/PRODUCT}</PRODUCT>

       <CAPACITY>{$i/CAPACITY}</CAPACITY>

       <ORIGINAL>{$i/PRICE}</ORIGINAL>

       <PRICE>{$i/PRICE - $discount }</PRICE>

       </OUTPUT>'

       PASSING DRIVE_DETAILS 

       COLUMNS 

                 Product  VARCHAR2(100) PATH 'PRODUCT',

                 Capacity VARCHAR2(20) PATH 'CAPACITY',

                 ActualPrice NUMBER PATH 'ORIGINAL',

                 Price NUMBER PATH 'PRICE') portable_drives /* Actual price is applied as a discount percentage now */

The output is shown below (which is what we were after)

PRODUCT	CAPACITY	ACTUALPRICE	PRICE
WD Elements SE 2.5" 500GB HD Portable USB Drive	500 GB	79	71.1
WD Elements SE 750GB Portable Hard Drive	750 GB	99	91.08
SEAGATE Expansion 2TB Desktop Hard Drive	2 TB	139	122.32
SEAGATE GoFlex 500GB Portable Black	500 GB	99	93.06
Iomega Select 34966 1TB External Desktop HD	1 TB	70	66.5

This example summarizes XMLSequence, XMLTABLE, EXTRACTVALUE, XMLTABLE with XQuery usages applied to a common problem to obtain the same solution. Stay connected to the blog for more..(Follow our blog..with email subscriptions now..look to your right)..

Did you find a new approach of solving the same other than these four? Post it to us in comments..Your support helps us to give better content.