XMLType Table XMLSequence Example - Oracle

Everybody loves XML..but when it comes to extracting data from Oracle XMLTYPE column, some of us could have a fear of starting. I will try to explain how to use XMLSequence in your SQL queries to pull data out from a table in Oracle, with an example.

For the purpose of our demonstration, we will create a simple product table holding some products..

CREATE TABLE PROD_XML_TAB
(
TEST_FIELD XMLTYPE
)

We now insert couple of XMLs into our table of XMLTYPE, I like iPhones and we create one for it…

INSERT INTO PROD_XML_TAB(TEST_FIELD)
VALUES(
XMLType('
<Product ProductCode="100">
 <BrandName>iPhone</BrandName>
 <ProductList>
  <Item ItemNo="100A"><ItemName>iPhone 3G</ItemName><Price>1000</Price></Item>
  <Item ItemNo="100B"><ItemName>iPhone 3GS 8GB</ItemName><Price>1200</Price></Item>
  <Item ItemNo="100C"><ItemName>iPhone 3GS 16GB</ItemName><Price>1300</Price></Item>
  <Item ItemNo="100D"><ItemName>iPhone 4 16GB</ItemName><Price>1500</Price></Item>
</ProductList>
</Product>'))

We will create one more XML for laptop products on the same lines

INSERT INTO PROD_XML_TAB(TEST_FIELD)
VALUES(
XMLType('
<Product ProductCode="200">
 <BrandName>Notebook</BrandName>
 <ProductList>
  <Item ItemNo="200A"><ItemName>Dell</ItemName><Price>900</Price></Item>
  <Item ItemNo="200B"><ItemName>HP</ItemName><Price>700</Price></Item>
  <Item ItemNo="200C"><ItemName>Toshiba</ItemName><Price>650</Price></Item>
  <Item ItemNo="200D"><ItemName>Sony Vio</ItemName><Price>750</Price></Item>
</ProductList>
</Product>'))

Now, what we would like to achieve using Oracle XMLSequence is the following..Give us a simple SQL that will provide the output in the following format (and without these colors, of course)

Product Code	Item No	Item Name
100	100A	iPhone 3G
100	100B	iPhone 3GS 8GB
100	100C	iPhone 3GS 16GB
100	100D	iPhone 4 16GB
200	200A	Dell
200	200B	HP
200	200C	Toshiba
200	200D	Sony Vio

Let us first see how to do it with an SQL and then offer an explanation for our process. So, the SQL to get the report in the way we were after is provided below

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

The output of this Query is given below

100     100A   iPhone 3G

100     100B   iPhone 3GS 8GB

100     100C   iPhone 3GS 16GB

100     100D   iPhone 4 16GB

200     200A   Dell

200     200B   HP

200     200C   Toshiba

200     200D   Sony Vio

Let me try to explain what we have done in this Query:

The "TABLE" function will get applied for every row in PROD_XML_TAB table. The extract function creates another XMLTYPE instance (subset of our XML) which contains all the "Item" elements ..(Item tags..to keep it simple). When this is fed to the XMLSequence, it creates a collection of all Items. The TABLE function expands this list into multiple rows which gets joined to the PROD_XML_TAB table. This means, when this query gets executed we get a list of all products and their corresponding item codes / name. This is a very simple example that illustrates how to use XMLSequence to extract data from XMLType column of a table in Oracle. More complex examples soon..

Did you use XMLSequence for your queries? If you'd encountered a complex scenario, can you share it? We will have a look.