Oracle SQL XMLQuery Usage With Examples Part 4

This is part 4 of XMLQuery usage techniques and refer to the previous part (XMLQuery – Part 3) to grab the table data that we will be using for the examples in this post.

We saw how to use nested for loops to grab XML Data from two tables. Just to compare XMLQuery with XMLTABLE, consider the following SQL below

SELECT *   from XMLTABLE('for $i in fn:collection("oradb:/HR/POPULAR_BRAND"),                      $j in fn:collection("oradb:/HR/POPULAR_PRODUCTS")                    where $i/ROW/BRAND_ID = $j/ROW/BRAND_ID                      and $i/ROW/BRAND_ID = "1"                    return <LIST Brand = "{$j/ROW/PRODUCT_TYPE}">{$i/ROW/BRAND_NAME,$j/ROW/PRODUCT_DESCR,$j/ROW/PRODUCT_PRICE}</LIST>')

Contrasting to XMLQuery, this SQL returns two rows one for each HP Notebook Model in POPULAR_PRODUCTS table. The output will be two fragments of XML as shown below

<LIST Brand="LAPTOP"><BRAND_NAME>HP</BRAND_NAME><PRODUCT_DESCR>HP ProBook 4525s</PRODUCT_DESCR><PRODUCT_PRICE>500</PRODUCT_PRICE></LIST>

<LIST Brand="LAPTOP"><BRAND_NAME>HP</BRAND_NAME><PRODUCT_DESCR>HP ProBook 4720s</PRODUCT_DESCR><PRODUCT_PRICE>650</PRODUCT_PRICE></LIST>

We can now see an example that explains how to pass dynamic values (Brand ID for example) in the SQL. Consider the sequence below

SQL> DEFINE BRANDID='2' SQL> SELECT XMLQuery('for $i in fn:collection("oradb:/HR/POPULAR_BRAND"),   2                       $j in fn:collection("oradb:/HR/POPULAR_PRODUCTS")   3                     where $i/ROW/BRAND_ID = $j/ROW/BRAND_ID   4                       and $i/ROW/BRAND_ID = $BrandID   5                     return <LIST Brand = "{$j/ROW/PRODUCT_TYPE}">{$i/ROW/BRAND_NAME,$j/ROW/PRODUCT_DESCR,$j/ROW/PRODUCT_PRICE}</LIST>'   6                     PASSING CAST('&BRANDID' AS VARCHAR2(10)) as "BrandID"   7                    RETURNING CONTENT) AS "Acer Products"   8  FROM DUAL   9  /

We are introduced to some new concepts here. We first define a variable BRANDID that holds the Brand ID of Acer Notebooks. We then use PASSING construct inside XMLQuery and pass the variable. This value is then fed in the XQuery string as $BrandID. Not much complex to understand, the output of this SQL example is provided below

<LIST Brand="LAPTOP">                    <BRAND_NAME>Acer</BRAND_NAME>                    <PRODUCT_DESCR>Acer Aspire AS5742Z-4813</PRODUCT_DESCR>                    <PRODUCT_PRICE>500</PRODUCT_PRICE>           </LIST>           <LIST Brand="LAPTOP">                    <BRAND_NAME>Acer</BRAND_NAME>                    <PRODUCT_DESCR>Acer Aspire AS5253-BZ660</PRODUCT_DESCR>                    <PRODUCT_PRICE>650</PRODUCT_PRICE>           </LIST>

In the next part of this series, we will see an interesting example..How to use XQuery to  read XMLTYPE column using XMLQuery…Interesting? Keep watching this space. Post your comments and follow our blog….