Oracle extractValue failing when query returns many rows

Question

I have the probably unenviable task of writing a data migration query to populate existing records with a value for a new column being added to a table on a production database. The table has somewhere in the order of 200,000 rows.

The source of the value is in some XML that is stored in the database. I have some XPath that will pull out the value I want, and using extractValue to get the value out seems all good, until the number of records being updated by the query starts getting larger than the few I had in my test DB.

Once the record set grows to some random amount of size, somewhere around 500 rows, then I start getting the error "ORA-19025: EXTRACTVALUE returns value of only one node". Figuring that there was some odd row in the database for which there wasn't a unique result for the XPath, I tried to limit down the records in the query to isolate the bad record. But as soon as I shrunk the record set, the error disappeared. There is actually no row that will return more than one value for this XPath query. It looks like there's something fishy happening with extractValue.

Does anyone know anything about this? I'm not an SQL expert, and even then have spent more time on SQL Server than Oracle. So if I can't get this to work I guess I'm left to do some messing with cursors or something.

Any advice/help? If it helps, we're running 10g on the server. Thanks!

Answer 1

It's almost certain that at least one row from the table with the source XML has an invalid value. Rewrite the query to try and find it.

For example, use the XMLPath predicate that would give you the node in the second position (I don't currently have access to a db with XML objects, so I can't guarantee 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;