Oracle XMLQuery Examples Part 3

Continuing our XMLQuery examples, we now present an approach that shows how to use nested ‘for’ loops to get structured output. To make this example easier to understand, we truncate the POPULAR_BRAND and POPULAR_PRODUCTS table to have some data that is easier for us to understand. So, truncate these two tables and insert the rows as shown below

TRUNCATE TABLE POPULAR_BRAND / INSERT INTO POPULAR_BRAND VALUES ('1','HP') INSERT INTO POPULAR_BRAND VALUES ('2','Acer') INSERT INTO POPULAR_BRAND VALUES ('3','ASUS') INSERT INTO POPULAR_BRAND VALUES ('4','Sony') / TRUNCATE TABLE POPULAR_PRODUCTS / INSERT INTO POPULAR_PRODUCTS VALUES ('1','LAPTOP','HP ProBook 4525s','500') INSERT INTO POPULAR_PRODUCTS VALUES ('1','LAPTOP','HP ProBook 4720s','650') INSERT INTO POPULAR_PRODUCTS VALUES ('2','LAPTOP','Acer Aspire AS5742Z-4813','500') INSERT INTO POPULAR_PRODUCTS VALUES ('2','LAPTOP','Acer Aspire AS5253-BZ660','650') INSERT INTO POPULAR_PRODUCTS VALUES ('3','LAPTOP','ASUS A52F-XT22','550') INSERT INTO POPULAR_PRODUCTS VALUES ('3','LAPTOP','ASUS G53JW-XT1','675') INSERT INTO POPULAR_PRODUCTS VALUES ('4','LAPTOP','Sony VAIO VPCEC3DFX/BJ','685')

We will now present an example that does a nested loop to return values under each ‘Notebook’. The SQL is given below

SELECT XMLQuery('for $i in fn:collection("oradb:/HR/POPULAR_BRAND")/ROW /* For every row in POPULAR_BRAND table */           return <Notebook id="{$i/BRAND_ID}"> /* Stamp Brand ID as attribute of Notebook */                    <Laptop>                      {for $j in fn:collection("oradb:/HR/POPULAR_PRODUCTS")/ROW /* Loop through POPULAR_PRODUCTS */                       where $j/BRAND_ID eq $i/BRAND_ID /* Pick rows where there is BRAND ID Match */                       return ($j/PRODUCT_DESCR, $j/PRODUCT_PRICE)} /* Return description and price from products table for all matches */                    </Laptop>                     </Notebook>' RETURNING CONTENT)   FROM DUAL;

The output of the SQL above is shown below

<Notebook id="1">                         <Laptop>                                     <PRODUCT_DESCR>HP ProBook 4525s</PRODUCT_DESCR>                                     <PRODUCT_PRICE>500</PRODUCT_PRICE>                                     <PRODUCT_DESCR>HP ProBook 4720s</PRODUCT_DESCR>                                     <PRODUCT_PRICE>650</PRODUCT_PRICE>                         </Laptop>             </Notebook>             <Notebook id="2">                         <Laptop>                                     <PRODUCT_DESCR>Acer Aspire AS5742Z-4813</PRODUCT_DESCR>                                     <PRODUCT_PRICE>500</PRODUCT_PRICE>                                     <PRODUCT_DESCR>Acer Aspire AS5253-BZ660</PRODUCT_DESCR>                                     <PRODUCT_PRICE>650</PRODUCT_PRICE>                         </Laptop>             </Notebook>             <Notebook id="3">                         <Laptop>                                     <PRODUCT_DESCR>ASUS A52F-XT22</PRODUCT_DESCR>                                     <PRODUCT_PRICE>550</PRODUCT_PRICE>                                     <PRODUCT_DESCR>ASUS G53JW-XT1</PRODUCT_DESCR>                                     <PRODUCT_PRICE>675</PRODUCT_PRICE>                         </Laptop>             </Notebook>             <Notebook id="4">                         <Laptop>                                     <PRODUCT_DESCR>Sony VAIO VPCEC3DFX/BJ</PRODUCT_DESCR>                                     <PRODUCT_PRICE>685</PRODUCT_PRICE>                         </Laptop>             </Notebook>

Where a brand contains more than one laptop, would it not be nice to separate the “Laptop” structure further? (Highlighted above) The SQL to do this is shown below

SELECT XMLQuery('for $i in fn:collection("oradb:/HR/POPULAR_BRAND")/ROW           return <Notebook id="{$i/BRAND_ID}">                    <Laptop>                      {for $j in fn:collection("oradb:/HR/POPULAR_PRODUCTS")/ROW                       where $j/BRAND_ID eq $i/BRAND_ID                       return <LIST>{($j/PRODUCT_DESCR, $j/PRODUCT_PRICE)}</LIST>}                    </Laptop>                     </Notebook>' RETURNING CONTENT) FROM DUAL;

This SQL produces an output as shown below (only a portion of the output is shown below for illustration)

……..            <Notebook id="3">                         <Laptop>                                     <LIST>                                                 <PRODUCT_DESCR>ASUS A52F-XT22</PRODUCT_DESCR>                                                 <PRODUCT_PRICE>550</PRODUCT_PRICE>                                     </LIST>                                     <LIST>                                                 <PRODUCT_DESCR>ASUS G53JW-XT1</PRODUCT_DESCR>                                                 <PRODUCT_PRICE>675</PRODUCT_PRICE>                                     </LIST>                         </Laptop>             </Notebook> ……

More examples on XMLQuery to follow..