Oracle XMLQuery Example Usage Part 1

In this short tutorial, we will explain the usage of XMLQuery function in Oracle, with a sample relational table. We will aim to keep this illustration as simple as possible so that it will enable an easier understanding of XMLQuery concepts. As we move on in subsequent tutorial sections, we will present some complex usages of XMLQuery and the benefits of XMLQuery. The examples presented in this tutorial are tested in 11g R2 version of Oracle

.

To start with let us create two relational tables and pump in some data. The tables we will be using are POPULAR_BRAND that lists some brand names and POPULAR_PRODUCTS that holds some products under each of these brands. Scripts below to start with;

CREATE TABLE POPULAR_BRAND ( BRAND_ID VARCHAR2(10), BRAND_NAME VARCHAR2(100) ) / CREATE TABLE POPULAR_PRODUCTS ( BRAND_ID VARCHAR2(10), PRODUCT_TYPE VARCHAR2(20), PRODUCT_DESCR VARCHAR2(100), PRODUCT_PRICE VARCHAR2(20) ) / INSERT INTO POPULAR_BRAND VALUES('1','Microsoft') INSERT INTO POPULAR_BRAND VALUES('2','Sony') INSERT INTO POPULAR_BRAND VALUES('3','Gateway') INSERT INTO POPULAR_BRAND VALUES('6','Lenovo') INSERT INTO POPULAR_BRAND VALUES('7','HP') / INSERT INTO POPULAR_PRODUCTS VALUES ('7','LAPTOP','HP Dual Core Laptop','500') INSERT INTO POPULAR_PRODUCTS VALUES ('7','LAPTOP','HP Dual Intel i5 PC','600') INSERT INTO POPULAR_PRODUCTS VALUES ('8','LAPTOP','Acer Aspire Laptop','450') INSERT INTO POPULAR_PRODUCTS VALUES ('1','Mouse','Microsoft Optical Mouse','30') INSERT INTO POPULAR_PRODUCTS VALUES ('7','Printer','HP Wireless Printer','85') INSERT INTO POPULAR_PRODUCTS VALUES ('6','External HDD','16 GB USB Drive','30') INSERT INTO POPULAR_PRODUCTS VALUES ('9','External HDD','2 TB Fantom HDD','100') INSERT INTO POPULAR_PRODUCTS VALUES ('2','SDHC','Sony SDHC Card','45') /

The objective for us would be to use XMLQuery and pull out all HP Products from POPULAR_PRODUCTS table. This may look easy but trust me, this is just a basic example of using XMLQuery..The capabilities are really more which we will definitely look upon when we move into complex examples. (remove the highlighted notes, when running the example)

SELECT XMLQuery('for $i in fn:collection("oradb:/HR/POPULAR_BRAND"), /* Get details from Brand Table */                      $j in fn:collection("oradb:/HR/POPULAR_PRODUCTS") /* Get details from Products Table */                    where $i/ROW/BRAND_ID = $j/ROW/BRAND_ID /* Join based on Brand ID */                      and $i/ROW/BRAND_ID = "7" /* Pass brand ID pertaining to HP Products */                    return $j' RETURNING CONTENT) AS "HP Products"     FROM DUAL

The output of this SQL is provided below..(We will see how to pass this input dynamically in one of the posts later)

<ROW>                    <BRAND_ID>7</BRAND_ID>                    <PRODUCT_TYPE>LAPTOP</PRODUCT_TYPE>                    <PRODUCT_DESCR>HP Dual Core Laptop</PRODUCT_DESCR>                    <PRODUCT_PRICE>500</PRODUCT_PRICE>           </ROW>           <ROW>                    <BRAND_ID>7</BRAND_ID>                    <PRODUCT_TYPE>LAPTOP</PRODUCT_TYPE>                    <PRODUCT_DESCR>HP Dual Intel i5 PC</PRODUCT_DESCR>                    <PRODUCT_PRICE>600</PRODUCT_PRICE>           </ROW>           <ROW>                    <BRAND_ID>7</BRAND_ID>                    <PRODUCT_TYPE>Printer</PRODUCT_TYPE>                    <PRODUCT_DESCR>HP Wireless Printer</PRODUCT_DESCR>                    <PRODUCT_PRICE>85</PRODUCT_PRICE>           </ROW>

which satisfies our objective.  This is a simple and basic example for XMLQuery usage. We will now expand this example to demonstrate some interesting examples.