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..

2 comments:

  1. Hi,

    this one works..

    WITH TB_TBL AS
    (select extractvalue(test_field, '/Product/@ProductCode') as product_code,
    extractvalue(value(a), '/ModelDetails/@ModelID') as model_id,
    extract(value(a), '/ModelDetails/Item') as item_Det
    from PROD_XML_TAB,
    table(xmlsequence(extract(test_field, '/Product/ModelDetails'))) a)
    select product_code,model_id,extractvalue(value(a1),'/Item/ItemName')
    from tb_tbl,table(xmlsequence(extract(item_Det, '/Item'))) a1

    ReplyDelete
  2. @Srini

    Great work Srini..Also check out here for solution to this XMLSequence Usage Question ..

    ReplyDelete