Oracle XMLSequence Example

In our previous post, we presented a simple example that showed how to pull out data from XMLTYPE field using XMLSequence function. The example shown in the previous post was a basic one that illustrated how simple the usage of XMLSequence could be, if it is used correctly.The XMLs used in the post were simple, and in this post, I'm going to provide a slightly complex example of using XMLSequence.

To start of, let us create a complex XML again, and insert into our test table PROD_XML_TAB. The XML is shown below

INSERT INTO PROD_XML_TAB(TEST_FIELD)
VALUES(
XMLType('<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></Item>
 </ModelDetails>
 <ModelDetails ModelID="100-2" ModelName="iPhone 3GS">
  <Item ItemNo="100-2-A"><ItemName>IPhone 3GS 16GB Black</ItemName><Price>900</Price></Item>
  <Item ItemNo="100-2-B"><ItemName>IPhone 3G S 16GB White</ItemName><Price>950</Price></Item>
  <Item ItemNo="100-2-C"><ItemName>IPhone 3G S 32GB Black</ItemName><Price>1000</Price></Item>
  <Item ItemNo="100-2-D"><ItemName>IPhone 3G S 32GB White</ItemName><Price>1050</Price></Item>
  </ModelDetails> 
  <ModelDetails ModelID="100-3" ModelName="IPhone 4">
  <Item ItemNo="100-3-A"><ItemName>IPhone 4 16GB White</ItemName><Price>1200</Price></Item>
  <Item ItemNo="100-3-B"><ItemName>IPhone 4 16GB Black</ItemName><Price>1200</Price></Item>
  <Item ItemNo="100-3-C"><ItemName>IPhone 4 32GB White</ItemName><Price>1400</Price></Item>
 </ModelDetails>
</Product>'))

We have  a single product "Iphone", three MODELs under that, 3G, 3Gs and 4. Our aim now would be to run a SQL Query against this table and produce a good looking output like the one shown below:

Product Code	ModelID	Item No	Item Name
100	100-1	100-1-A	IPhone 3G 8GB Black
100	100-2	100-2-A	IPhone 3GS 16GB Black
100	100-2	100-2-B	IPhone 3G S 16GB White
100	100-2	100-2-C	IPhone 3G S 32GB Black
100	100-2	100-2-D	IPhone 3G S 32GB White
100	100-3	100-3-A	IPhone 4 16GB White
100	100-3	100-3-B	IPhone 4 16GB Black
100	100-3	100-3-C	IPhone 4 32GB White

The tricky part here is that our XML has two dimensions to be retrieved before going into the data. Product Code, Model ID and then then Item Details. I tried a simple Query like this and got a strange output

SELECT extractValue(TEST_FIELD, '/Product/@ProductCode') AS ProductCode,
       extractValue(value(f1), '/ModelDetails/@ModelID') AS ItemNo,
       extractvalue(value(f2), '/Item/ItemName') AS ItemNo
  FROM PROD_XML_TAB,
       table(XMLSequence(extract(TEST_FIELD, '/Product/ModelDetails'))) f1,
       table(XMLSequence(extract(TEST_FIELD, '/Product/ModelDetails/Item'))) f2

100       100-1    IPhone 3G 8GB Black

100       100-1    IPhone 3GS 16GB Black

100       100-1    IPhone 3G S 16GB White

100       100-1    IPhone 3G S 32GB Black

100       100-1    IPhone 3G S 32GB White

100       100-1    IPhone 4 16GB White

100       100-1    IPhone 4 16GB Black

100       100-1    IPhone 4 32GB White

100       100-2    IPhone 3G 8GB Black

100       100-2    IPhone 3GS 16GB Black

100       100-2    IPhone 3G S 16GB White

100       100-2    IPhone 3G S 32GB Black

100       100-2    IPhone 3G S 32GB White

100       100-2    IPhone 4 16GB White

100       100-2    IPhone 4 16GB Black

100       100-2    IPhone 4 32GB White

100       100-3    IPhone 3G 8GB Black

100       100-3    IPhone 3GS 16GB Black

100       100-3    IPhone 3G S 16GB White

100       100-3    IPhone 3G S 32GB Black

100       100-3    IPhone 3G S 32GB White

100       100-3    IPhone 4 16GB White

100       100-3    IPhone 4 16GB Black

100       100-3    IPhone 4 32GB White

All the coloured rows are incorrect! :(..There is something wrong in the Query that we executed.

Do you know how to crack this and get the result we wanted? Post your answer..