In this post, we will demonstrate a slighlty complex method of querying XML data from Oracle using XMLSequence.
If you'd gone through the previous posts for querying XML Data using XMLSequence, this post would be easy enough to read through. We will continue to use our iphone XML example for this post, and the challenge would be to write a SQL Query based on this XML provided below:
<Product ProductCode="100">
<BrandName>IPhones</BrandName>
<ModelDetails ModelID="100-1" ModelName="iPhone 3G">
<Item ItemNo="100-1-A">
<ItemName>IPhone 3G 8GB Black</ItemName>
<Price>900</Price>
<Specifications>
<Spec IDNO='1'>8 GB Memory</Spec>
<Spec IDNO='2'>Latest iOS No Support</Spec>
<Spec IDNO='3'>Black Color</Spec>
</Specifications>
</Item>
</ModelDetails>
<ModelDetails ModelID="100-2" ModelName="iPhone 3GS">
<Item ItemNo="100-2-A"><ItemName>IPhone 3GS 16GB Black</ItemName><Price>900</Price>
<Specifications>
<Spec IDNO='1'>16 GB Memory</Spec>
<Spec IDNO='2'>Only One Camera</Spec>
<Spec IDNO='3'>Black Color</Spec>
</Specifications>
</Item>
<Item ItemNo="100-2-C"><ItemName>IPhone 3G S 32GB Black</ItemName><Price>1000</Price>
<Specifications>
<Spec IDNO='1'>32 GB Memory</Spec>
<Spec IDNO='2'>GameCenter Support</Spec>
<Spec IDNO='3'>Black Color</Spec>
<Spec IDNO='4'>Latest iOS Support</Spec>
</Specifications>
</Item>
</ModelDetails>
<ModelDetails ModelID="100-3" ModelName="IPhone 4">
<Item ItemNo="100-3-A"><ItemName>IPhone 4 16GB White</ItemName><Price>1200</Price>
<Specifications>
<Spec IDNO='1'>16 GB Memory</Spec>
<Spec IDNO='2'>Two Cameras</Spec>
<Spec IDNO='3'>White Color</Spec>
<Spec IDNO='4'>Latest iOS Support</Spec>
<Spec IDNO='5'>Brilliant Display</Spec>
</Specifications>
</Item>
<Item ItemNo="100-3-C"><ItemName>IPhone 4 32GB White</ItemName><Price>1400</Price>
<Specifications>
<Spec IDNO='1'>32 GB Memory</Spec>
<Spec IDNO='2'>Two Cameras</Spec>
<Spec IDNO='3'>White Color</Spec>
<Spec IDNO='4'>Latest iOS Support</Spec>
<Spec IDNO='5'>HD Recording</Spec>
</Specifications>
</Item>
</ModelDetails>
</Product>
to produce an output lists the following:
ProductCode Vs BrandName Vs ModelName Vs ItemName Vs (Comma separated list of Specifications). The output of the Query should be like the one provided below:
ProductCode | BrandName | ModelName | ItemName | Specifications |
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,GameCenter Support,Black Color,Latest iOS Support |
Give it a try and post your SQL Query to this question. I will post and explain my answer to this in my next post.
No comments:
Post a Comment