Oracle SQL XML Data XMLTYPE Final Solution

This post explains the usage of XMLSequence with an example.  We presented a complex XML and outlined the approach to extract data from XML using extractvalue and XMLSequence. We stopped the post at the last stage, presenting the reader an opportunity to fix the result as a comma separated value(CSV). In order to group specifications as a Comma separted value, we present two solutions in this post. The first solution works on a 11g R2 version only. The second one works on all versions of Oracle. (Make sure you check the linked post to understand the solution approach, otherwise, this SQL might look complex especially if you are a starter to XMLSequence)

Approach one - Applicable to 11g R2 only

select product_code,
       brand_name,
       model_name,
       Item_name,
       listagg(Specifications, ',') WITHIN
 GROUP(
 Order by Specifications) Specs
  from (select extractvalue(test_field, '/Product/@ProductCode') as Product_Code,
               extractvalue(test_field, '/Product/BrandName') as Brand_Name,
               extractvalue(value(a), '/ModelDetails/@ModelName') as Model_Name,
               extractvalue(value(b), 'Item/ItemName') as Item_Name,
               extractvalue(value(c), '/Spec') as Specifications
          from prod_xml_tab,
               table(xmlsequence(extract(test_field, '/Product/ModelDetails'))) a,
               table(xmlsequence(extract(a.column_value,
                                         '/ModelDetails/Item'))) b,
               table(xmlsequence(extract(b.column_value,
                                         '/Item/Specifications/Spec'))) c)
 Group by product_code, brand_name, model_name, Item_name

Approach tw- Oracle 9i and Above

SELECT PRODUCTCODE,
       BRANDNAME,
       MODELNAME,
       ITEMNAME,
       rtrim(xmlagg(xmlelement(e, specifications || ','))
             .extract('//text()'),
             ',')
  from (select extractvalue(test_field, '/Product/@ProductCode') as Product_Code,
               extractvalue(test_field, '/Product/BrandName') as Brand_Name,
               extractvalue(value(a), '/ModelDetails/@ModelName') as Model_Name,
               extractvalue(value(b), 'Item/ItemName') as Item_Name,
               extractvalue(value(c), '/Spec') as Specifications
          from prod_xml_tab,
               table(xmlsequence(extract(test_field, '/Product/ModelDetails'))) a,
               table(xmlsequence(extract(a.column_value,
                                         '/ModelDetails/Item'))) b,
               table(xmlsequence(extract(b.column_value,
                                         '/Item/Specifications/Spec'))) c)
 Group by product_code, brand_name, model_name, Item_name

Both these SQLs guarantee the output we are looking for, which is provided below

100      IPhones            IPhone 4           IPhone 4           16GB White      16 GB Memory,Two Cameras,White Color,Brilliant Display,Latest iOS Support

100      IPhones            IPhone 4           IPhone 4           32GB White      32 GB Memory,Two Cameras,Latest iOS Support,HD Recording,White Color

100      IPhones            iPhone 3G         IPhone 3G        8GB Black         8 GB Memory,Latest iOS No Support,Black Color

100      IPhones            iPhone 3GS       IPhone 3GS      16GB Black       16 GB Memory,Only One Camera,Black Color

100      IPhones            iPhone 3GS       IPhone 3G S     32GB Black       32 GB Memory,Black Color,Latest iOS Support,GameCenter Support

the exact report we were after, one row per model and a Comma separated list of specification list.

If you'd looked at the XMLSequence Challenge, we had a quite a few redundancy. May be the IPhone example does not explain that clearly, we will take up a different example this time. Let us construct an XML that groups common features outside the item list and see how to get the same report again. Stay connected to thinktibits to learn this.. Follow our blog and share your comments..