Essentially my problem is that in 9.2.0.3 (Standard Edition Database), the ExtractValue is not removing the xml tags for a select statement, whereas the 9.2.0.4 (Enterprise Edition Database) is. See below.
In 9.2.0.3 Standard Edition:
Running the following script;
1 select
2 extractValue(value(i),'/tax/batch_no'),
3 extractValue(value(i),'/tax/sequence_no'),
4 extractValue(value(i),'/tax/employee_no'),
5 extractValue(value(i),'/tax/paycode'),
6 extractValue(value(i),'/tax/taxscl')
7* from XML_TAX p, table(xmlsequence(extract(value(p),'/alesco/tax'))) i
Returns:
<batch_no>7361</batch_no>
<sequence_no>137</sequence_no>
<employee_no>2027510</employee_no>
<paycode>PAYG</paycode>
<taxscl>2</taxscl>
The problem is;
When I run the following in 9.2.0.4 Enterprise Edition, the following dataset is returned, running the same query (with slightly different data, but the tags are removed):
SQL> select
2 extractValue(value(i),'/tax/batch_no'),
3 extractValue(value(i),'/tax/sequence_no'),
4 extractValue(value(i),'/tax/employee_no'),
5 extractValue(value(i),'/tax/paycode'),
6 extractValue(value(i),'/tax/taxscl'),
7 to_date(extractValue(value(i),'/tax/start_date'),'DD/MM/YYYY')
8 from XML_TAX p, table(xmlsequence(extract(value(p),'/alesco/tax'))) i;
EXTRACTVALUE(VALUE(I),'/TAX/BATCH_NO')
--------------------------------------------------------------------------------
--------------------
EXTRACTVALUE(VALUE(I),'/TAX/SEQUENCE_NO')
--------------------------------------------------------------------------------
--------------------
EXTRACTVALUE(VALUE(I),'/TAX/EMPLOYEE_NO')
--------------------------------------------------------------------------------
--------------------
EXTRACTVALUE(VALUE(I),'/TAX/PAYCODE')
--------------------------------------------------------------------------------
--------------------
EXTRACTVALUE(VALUE(I),'/TAX/TAXSCL')
--------------------------------------------------------------------------------
--------------------
TO_DATE(E
---------
1011
1
2000034
TAX
1
01-DEC-03
Which is correct and what I expect, as I can now use these variables as to insert etc into other tables.
Is there an issue with 9.2.0.3 as opposed to 9.2.0.4 ? Or Standard / Enterprise Edition of the databases for XDB calls?
In partially answering my question; Obviously there is a a difference, but is it documented bug anywhere, or is there a workaround?
Any advise is appreciated.