Oracle SQL XMLQuery Usage Part 2

We earlier presented a simple introduction to XMLQuery usage in Oracle. We will present some more examples for XMLQuery in this post. Make sure that you have the required tables to test out these examples. The table details with insert scripts can be found in Part 1 (XMLQuery). 

The first sample XMLQuery example pulled out all the HP products with details such as BRAND ID, PRODUCT TYPE, PRODUCT DESCRIPTION and PRODUCT PRICE. Though this looks good, BRAND ID is a relational join column and it would be good if it is replaced with BRAND NAME.. We will see how to improvise the initial SQL’s return clause to achieve this objective

SELECT XMLQuery('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 = "7"                    return ($i/ROW/BRAND_NAME,$j/ROW/PRODUCT_TYPE,$j/ROW/PRODUCT_DESCR,$j/ROW/PRODUCT_PRICE)' RETURNING CONTENT) AS "HP Products" FROM DUAL

The change to the original SQL is shown above, and the output is

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

If you examine the output above, it is not structured. What if you want a starting tag like <LIST> around each BRAND NAME..The SQL below shows how to do this

SELECT XMLQuery('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 = "7"                    return <LIST>{$i/ROW/BRAND_NAME,$j/ROW/PRODUCT_TYPE,$j/ROW/PRODUCT_DESCR,$j/ROW/PRODUCT_PRICE}</LIST>'                   RETURNING CONTENT) AS "HP Products" FROM DUAL

The curly brackets ({ } ) are important as it tells Oracle to substitute the values. The output is shown below

<LIST>                    <BRAND_NAME>HP</BRAND_NAME>                    <PRODUCT_TYPE>LAPTOP</PRODUCT_TYPE>                    <PRODUCT_DESCR>HP Dual Core Laptop</PRODUCT_DESCR>                    <PRODUCT_PRICE>500</PRODUCT_PRICE>           </LIST>           <LIST>                    <BRAND_NAME>HP</BRAND_NAME>                    <PRODUCT_TYPE>LAPTOP</PRODUCT_TYPE>                    <PRODUCT_DESCR>HP Dual Intel i5 PC</PRODUCT_DESCR>                    <PRODUCT_PRICE>600</PRODUCT_PRICE>           </LIST>           <LIST>                    <BRAND_NAME>HP</BRAND_NAME>                    <PRODUCT_TYPE>Printer</PRODUCT_TYPE>                    <PRODUCT_DESCR>HP Wireless Printer</PRODUCT_DESCR>                    <PRODUCT_PRICE>85</PRODUCT_PRICE>           </LIST>

What should be done, if you want Product Type as attribute of LIST Tag? Let us add some more rows into POPULAR_PRODUCTS to explain this

INSERT INTO POPULAR_PRODUCTS VALUES ('1','Software','Microsoft Windows 7 Ultimate','150') INSERT INTO POPULAR_PRODUCTS VALUES ('1','Office','Microsoft Office 2010','100') INSERT INTO POPULAR_PRODUCTS VALUES ('1','Keyboard','Microsoft Keyboard','75')

The SQL to get product type as attribute is provided below with the output:- (the changes are highlighted below)

SELECT XMLQuery('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>'                   RETURNING CONTENT) AS "Microsoft Products" FROM DUAL

The output of this XMLQuery SQL is as expected

<LIST Brand="Mouse">                    <BRAND_NAME>Microsoft</BRAND_NAME>                    <PRODUCT_DESCR>Microsoft Optical Mouse</PRODUCT_DESCR>                    <PRODUCT_PRICE>30</PRODUCT_PRICE>           </LIST>           <LIST Brand="Software">                    <BRAND_NAME>Microsoft</BRAND_NAME>                    <PRODUCT_DESCR>Microsoft Windows 7 Ultimate</PRODUCT_DESCR>                    <PRODUCT_PRICE>150</PRODUCT_PRICE>           </LIST>           <LIST Brand="Office">                    <BRAND_NAME>Microsoft</BRAND_NAME>                    <PRODUCT_DESCR>Microsoft Office 2010</PRODUCT_DESCR>                    <PRODUCT_PRICE>100</PRODUCT_PRICE>           </LIST>           <LIST Brand="Keyboard">                    <BRAND_NAME>Microsoft</BRAND_NAME>                    <PRODUCT_DESCR>Microsoft Keyboard</PRODUCT_DESCR>                    <PRODUCT_PRICE>75</PRODUCT_PRICE>           </LIST>

More XMLQuery Examples to follow..