Oracle XMLSequence Usage - Solution

For our XMLSequence example question posted in this blog earlier, I've been getting a lot of responses and felt I would give my solution to this problem. If you are landing on this page directly, I would suggest you to go through the question first.

The XML we outlined is produced again 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></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>

Note: The solution I'm giving below is not the only solution. Many variations exist. To get the output in the format that was requested, following SQL Query can be used:

select extractvalue(test_field, '/Product/@ProductCode') as product_code,
       extractvalue(value(a), '/ModelDetails/@ModelID') as model_id,
       extractvalue(value(c), '/Item/ItemName') as ItemName
  from PROD_XML_TAB,
       table(xmlsequence(extract(test_field, '/Product/ModelDetails'))) a,
       table(xmlsequence(extract(a.column_value, '//Item'))) c

The output of this SQL is provided below

100    100-1          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-3          IPhone 4 16GB White

100    100-3          IPhone 4 16GB Black

100    100-3          IPhone 4 32GB White

and this matches to what we are after!..If you look at the SQL (before running through the explanation, make sure that you understand the simple XMLSequence example solution posted earlier), we have used a new pseudocolumn called "column_value".

An extract of this pseudocolumn from Oracle documentation is given below

When you refer to an XMLTable construct without the COLUMNS clause, or when you use the TABLE function to refer to a scalar nested table type, the database returns a virtual table with a single column. This name of this pseudocolumn is COLUMN_VALUE.

We have to break down our XML to read the "ModelDetails" first, and break it down again to read all the "Item" under the Model Details. To do this, we use one TABLE(XMLSEQUENCE) (marked in grey in the query) to read the ModelDetails and one more TABLE(XMLSEQUENCE), the input for which comes from the first TABLE(XMLSEQUENCE) (marked in grey). Now, to get the extracted ModelDetails from the first one, we use the pseudocolumn COLUMN_VALUE. Basically, it is a serial split on the XML, breaking down into tiny fragments and combining them all in a single SQL to get the output.

Do you have any other interesting way to solve this problem? Share..

We will increase the complexity level and run through a slightly complex XMLSequence usage in our next post. Keep watching this space.