Ora-19025
When you work with xml file you should be solve the scenery that have an node with its properly sub node.
In our case the following nodes (
It is quite delicate find exactly approach that gives to you the requirement expected. The problems that I have got they were two different:
– Duplicate row
– ORA-19025 EXTRACTVALUE returns value of only one node or ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence
The following image shows the scenery described:
Duplicate row means the query extract the values that are not included on your node like these in red. Check the script duplicateRow.sql to understand the error raised.
– OrgnlRcrdId|Sts: 20170224MICMTSCINS871|RJCT
– TEMP:INS-105;It contains an invalid code
– TEMP:INS-106;The field is not registered at the system
– TEMP:INS-111;The field is not registered at the sub system
– TEMP:INS-107;thusday
– TEMP:INS-187;monday
– TEMP:INS-000;017.001.01
Appetency there not error because the query extract values but if we are going to do data analyst the query does not stop its self at the properly step generated duplicate rows.
Others error is the follow:
ORA-19025: EXTRACTVALUE returns value of only one node
It generated when we consider to extract value from VldtnRule/Id node, to replicate the error just run the following script:
ORA-19025 EXTRACTVALUE returns value of only one node.sql
To solve ORA-19025 the issue you have to manage sub node (
To implement the solution our case changed, it will be show below:
-
i. VldtnRule XMLTYPE PATH ‘VldtnRule’
ii. XMLTABLE(
‘VldtnRule’
PASSING q.VldtnRule
COLUMNS Idd VARCHAR2(30) path ‘./Id’,
Descc VARCHAR2(100) path ‘./Desc’ )
iii. LEFT JOIN XMLTABLE(
‘VldtnRule’
PASSING q.VldtnRule
COLUMNS Idd VARCHAR2(30) path ‘./Id’,
Descc VARCHAR2(100) path ‘./Desc’ ) v
ON (1=1)
Following instraction on the following script to have panoramic about this last point:
Download the script tosolveORA-19025.sql