ORA-19025: EXTRACTVALUE returns value of only one node

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It's not the first time I write about ORA-19025, however I believe that what follows is an interesting and not so common situation.

Imagine you receive an XML file that have been working perfectly for 3 years and suddenly it throws out the infamous exception:

ORA-19025: EXTRACTVALUE returns value of only one node

After mumbling and rummaging in your bullet-proof, supertested code, you come up with a query that at least pinpoints the offending XML node:

The problem is that for 3 years the title element never contained child nodes other than a single text node, then suddenly, someone managed to enter that sup element. You can easily try yourself:

ORA-19025: EXTRACTVALUE returns value of only one node

A quick and dirty workaround consists in adding XPath's text() function:

X
-----------------------------------------
Don't be  by unwanted tags in a text node 

This will keep you going, at the cost of stripping out the text inside the inner element(s). The problem with XML files is in that a stupid problem like that above makes unusable the whole lot even if the erroneous information is negligible.

If loosing some text is not an option, I fear that the only way forward is to process these nodes through a XSL transform that will strip out the tags and copy the text nodes inside.
May be I'll switch to this fancier option later on, for the moment I am just happy to keep the ball rolling without worrying too much about loosing an insignificant text node out of millions, in the end it's their fault if they added unexpected stuff inside that element.

See message translations for ORA-19025 and search additional resources.

ORA-19025: EXTRACTVALUE returns value of only one node

This error occurs when you try to extract scalar values from an XML fragment containing multiple nodes.
In order to simulate error, try executing the following query in a schema where you have at least two tables:

SELECT extractvalue(
     xmltype.createXML(
      dbms_xmlgen.getxml('select * from user_tables'))
     ,'/ROWSET/ROW/TABLE_NAME')
FROM dual;

ORA-19025: EXTRACTVALUE returns value of only one node

Indeed if you add a small XPath condition to the query above, you can get a valid result:

SELECT extractvalue(
     xmltype.createXML(
      dbms_xmlgen.getxml('select * from user_tables'))
     ,'/ROWSET/ROW[1]/TABLE_NAME')
FROM dual;

This query will extract the table name of the first TABLE_NAME node.

If you are trying to extract all table names from this XML file, then you must rewrite the query as i showed in a previous topic.

See message translations for ORA-19025 and search additional resources.

---

ORA-19025: EXTRACTVALUE restituisce il valore di un solo nodo
ORA-19025: EXTRACTVALUE devuelve un valor de un solo nodo
ORA-19025: EXTRACTVALUE retorna el valor de només un node
ORA-19025: EXTRACTVALUE renvoie la valeur d'un seul noeud
ORA-19025: EXTRACTVALUE gibt Wert von nur einem Knoten zurück
ORA-19025: Η τιμή EXTRACTVALUE επιστρέφει τιμή με μόνο ένα κόμβο
ORA-19025: EXTRACTVALUE returnerer kun værdi for én node
ORA-19025: EXTRACTVALUE returnerar värdet för bara en nod
ORA-19025: EXTRACTVALUE returnerer en verdi for bare én node
ORA-19025: EXTRACTVALUE palauttaa vain yhden solmun arvon
ORA-19025: Az EXTRACTVALUE csak egy csomópont értékét adja vissza.
ORA-19025: EXTRACTVALUE returnează valoarea pentru un singur nod
ORA-19025: EXTRACTVALUE retourneert waarde van slechts één node.
ORA-19025: EXTRACTVALUE retorna valor de apenas um nó
ORA-19025: EXTRACTVALUE devolve o valor de um único nó
ORA-19025: EXTRACTVALUE возвращает значение только одного узла
ORA-19025: EXTRACTVALUE vrací pouze hodnotu jednoho uzlu
ORA-19025: EXTRACTVALUE vracia hodnotu len jedného uzla
ORA-19025: EXTRACTVALUE zwraca wartość tylko jednego węzła
ORA-19025: EXTRACTVALUE sadece tek bir düğümün değerini döndürür
ORA-19025: EXTRACTVALUE returns value of only one node