Oracle XMLTABLE Usage Part 2

We are trying to explain the usage of XMLTABLE to understand the benefits it offers over XMLSequence. If you had referred to part 1 of this series, we presented a simple XML and came up with a report by using XMLTable. We also presented an alternative approach to achieve the same objective using XMLSequence. 

Now, when using XMLTable you may get the following errors

ORA-19228: XPST0008 - undeclared identifier: prefix '.' local-name '' Explanation: PASSING construct is not used:Make sure that you pass your "input" to XMLTABLE function..It requires an XML input to act on.

ORA-02000: missing COLUMNS keyword Explanation: The XMLTABLE construct requires the COLUMN keyword to be passed and when this is not provided, it results in the following error

If you had referred to our sample XML, we had four “Notebook” tags, two each for HP and Toshiba brands. Now, what if we need a running sequence number as well in the output, so that we can identify a row uniquely.We can add a “rownum” to our SQL and come up with a Query like this

SELECT rownum,NOTEBOOKS.*   FROM PRODUCT_DETAILS PO,        XMLTable('//Notebook' PASSING PO.NOTEBOOK        COLUMNS                 "BrandType"    CHAR(10) PATH '@Brand',                 "ProductModel" CHAR(50) PATH '@Model',                 "Harddisk" CHAR(10) PATH 'Harddisk',                 "Processor" CHAR(20) PATH 'Processor',                 "RAM" CHAR(10) PATH 'RAM') AS NOTEBOOKS

Alternatively, we can do this with “FOR ORDINALITY” keyword, an example of such an usage is provided below

SELECT NOTEBOOKS.*   FROM PRODUCT_DETAILS PO,        XMLTable('//Notebook' PASSING PO.NOTEBOOK        COLUMNS                "Seqno" FOR ORDINALITY,                             "BrandType"    CHAR(10) PATH '@Brand',                 "ProductModel" CHAR(50) PATH '@Model',                 "Harddisk" CHAR(10) PATH 'Harddisk',                 "Processor" CHAR(20) PATH 'Processor',                 "RAM" CHAR(10) PATH 'RAM') AS NOTEBOOKS

Both these SQLs result in the output we are after as shown below

Seqno	BrandType	ProductModel	Harddisk	Processor	RAM
1	HP	Pavilion dv6-3132TX Notebook	640 GB	Intel Core i7	4 GB
2	HP	HP Pavilion dv6-3032TX Notebook	640 GB	Intel Core i7	6 GB
3	Toshiba	Satellite A660/07R 3D Notebook	640 GB	Intel Core i7	4 GB
4	Toshiba	Satellite A660/15J Notebook	640 GB	Intel Core i5	6 GB

There is an interesting note in 11g Release 2 in XMLTABLE reference section;

Prior to Oracle Database 11g Release 2, some users employed Oracle SQL function XMLSequence within a SQL TABLE collection expression, that is, TABLE(XMLSequence(...)), to do some of what can be done better using SQL/XML function XMLTable. Function XMLSequence is deprecated in Oracle Database 11g Release 2.

In our sample XML, consider that the Brand Toshiba is not available for the fourth ‘Notebook’ tag. In this case, when a value for ‘Brand’ attribute is not available, we want to default it with “Sony”. (for example). To do this, we specify a clause “DEFAULT” in the COLUMNS section as shown below

SELECT NOTEBOOKS.*   FROM PRODUCT_DETAILS PO,        XMLTable('//Notebook' PASSING PO.NOTEBOOK        COLUMNS                 "Seqno" FOR ORDINALITY,                             "BrandType"    CHAR(10) PATH '@Brand' DEFAULT 'Sony',                 "ProductModel" CHAR(50) PATH '@Model',                 "Harddisk" CHAR(10) PATH 'Harddisk',                 "Processor" CHAR(20) PATH 'Processor',                 "RAM" CHAR(10) PATH 'RAM') AS NOTEBOOKS

So, the fourth row in our output gets modified as shown below

Seqno	BrandType	ProductModel	Harddisk	Processor	RAM
1	HP	Pavilion dv6-3132TX Notebook	640 GB	Intel Core i7	4 GB
2	HP	HP Pavilion dv6-3032TX Notebook	640 GB	Intel Core i7	6 GB
3	Toshiba	Satellite A660/07R 3D Notebook	640 GB	Intel Core i7	4 GB
4	Sony	Satellite A660/15J Notebook	640 GB	Intel Core i5	6 GB

More to follow…