If you had referred to the XML SQL Question that we asked in previous post, we have been given a sizeable XML and told to produce a report out of it. The final resulting XML would be big, but the solution can be easily understood by breaking it into finer chunks. We will aim to provide the tiny blocks and come up with one final SQL in this post.
We start with creating our test table and copy the XML provided in the reference post into it. The CREATE TABLE statement alone is provided below:
| CREATE TABLE PROD_XML_TAB ( TEST_FIELD XMLTYPE ) |
Our output should contain product code, brand name , model name, Item name and Comma separate list of specifications. Product Code and Brand name are available at the top level and they are not repeating.(for this example). We will fetch them first with a SQL sample (Note, we will be building this SQL and include more XML constructs)
select extractvalue(test_field, '/Product/@ProductCode') as Product_Code, => Taken the attribute ProductCode from the XML extractvalue(test_field, '/Product/BrandName') as Brand_Name => Taken the Tag Value for BrandName from prod_xml_tab Output 100 IPhones |
Great, we have made a start. We have got the Product Code and Brand Name in the output. We will refine this SQL now to get the Model Name.
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 => Model Name attribute is read here from prod_xml_tab, table(xmlsequence(extract(test_field, '/Product/ModelDetails'))) a => XML Table of Model Details using XMLSequence Output 100 IPhones iPhone 3G 100 IPhones iPhone 3GS 100 IPhones IPhone 4 |
So far, so good. In the next step, we get into each “ModelDetails” tag, and read the list of “ItemName” from it. We refine our SQL to include another XMLSEQUENCE table now
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 => Item Name Tag is read here from prod_xml_tab, table(xmlsequence(extract(test_field, '/Product/ModelDetails'))) a, table(xmlsequence(extract(a.column_value,'/ModelDetails/Item')))b –> Read every ITEM from MODEL Details into XMLTable Output 100 IPhones iPhone 3G IPhone 3G 8GB Black 100 IPhones iPhone 3GS IPhone 3GS 16GB Black 100 IPhones iPhone 3GS IPhone 3G S 32GB Black 100 IPhones IPhone 4 IPhone 4 16GB White 100 IPhones IPhone 4 IPhone 4 32GB White |
Nice. We will now read each of the “Specification” item inside every item using another XMLSequence. This is shown below..This takes us near to the output.
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 => Each specification tag is read here 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 => Read ever cluster of SPEC into XMLTable |
This gives us the following output
| 100 IPhones iPhone 3G IPhone 3G 8GB Black 8 GB Memory 100 IPhones iPhone 3G IPhone 3G 8GB Black Latest iOS No Support 100 IPhones iPhone 3G IPhone 3G 8GB Black Black Color 100 IPhones iPhone 3GS IPhone 3GS 16GB Black 16 GB Memory 100 IPhones iPhone 3GS IPhone 3GS 16GB Black Only One Camera 100 IPhones iPhone 3GS IPhone 3GS 16GB Black Black Color 100 IPhones iPhone 3GS IPhone 3G S 32GB Black 32 GB Memory 100 IPhones iPhone 3GS IPhone 3G S 32GB Black GameCenter Support 100 IPhones iPhone 3GS IPhone 3G S 32GB Black Black Color 100 IPhones iPhone 3GS IPhone 3G S 32GB Black Latest iOS Support 100 IPhones IPhone 4 IPhone 4 16GB White 16 GB Memory 100 IPhones IPhone 4 IPhone 4 16GB White Two Cameras 100 IPhones IPhone 4 IPhone 4 16GB White White Color 100 IPhones IPhone 4 IPhone 4 16GB White Latest iOS Support 100 IPhones IPhone 4 IPhone 4 16GB White Brilliant Display 100 IPhones IPhone 4 IPhone 4 32GB White 32 GB Memory 100 IPhones IPhone 4 IPhone 4 32GB White Two Cameras 100 IPhones IPhone 4 IPhone 4 32GB White White Color 100 IPhones IPhone 4 IPhone 4 32GB White Latest iOS Support 100 IPhones IPhone 4 IPhone 4 32GB White HD Recording |
As you see from the output above, we have got what we required..But the Specifications are not separated by comma..We need one row per Item and not one row per specification. How to do this?
Can you give a go from here and refine our final SQL? Post your SQL or wait for the solution..
No comments:
Post a Comment