Oracle extractValue does not work when a query returns many rows

Question

I have probably the unenviable task of writing a data transfer request to populate existing records with a value to add a new column to a table in a production database. The table holds about 200,000 rows.

The source of the value is in some XML that is stored in the database. I have XPath that will pull out the value that I want, and using extractValue to get the value seems good until the number of records updated on request is greater than the ones that I had in the test database.

As soon as the record set grows to some random size, somewhere around 500 lines, I start getting the error "ORA-19025: EXTRACTVALUE returns the value of only one node". Assuming there was some odd row in the database for which there was no unique result for XPath, I tried to limit the records in the query to isolate the bad record. But as soon as I compressed the recordset, the error disappeared. Actually there is no line which will return more than one value for this XPath request. There seems to be something suspicious going on with extractValue.

Does anyone know about this? I am not an expert on SQL, and even then I spent more time on SQL Server than on Oracle. So if I can't get this to work, I think I have to do something with cursors or something like that.

Any tips / help? If this helps, we run 10g on the server. Thanks!

Answer 1

I’m pretty sure that at least one row from the table with the original XML has an invalid value. Rewrite the query to find it.

For example, use the XMLPath predicate, which will provide you with a node in the second position (currently I do not have access to db with XML objects, so I can not guarantee that the following is syntactically perfect):

  SELECT SourceKey
       , EXTRACTVALUE(SourceXML, '/foo/bar/baz[1]')
       , EXTRACTVALUE(SourceXML, '/foo/bar/baz[2]')
    FROM SourceTable
   WHERE EXTRACTVALUE(SourceXML, '/foo/bar/baz[2]') IS NOT NULL;