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)..

Pointing upPointing upDid 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.

No comments:

Post a Comment