当查询返回多行时，Oracle extractValue 失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/995229/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle extractValue failing when query returns many rows
提问by Niall Connaughton
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.
我有一项可能令人羡慕的任务，即编写数据迁移查询以使用添加到生产数据库表中的新列的值填充现有记录。该表的某处大约有 200,000 行。
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.
值的来源是一些存储在数据库中的 XML。我有一些 XPath 可以提取我想要的值，并且使用 extractValue 来获取值似乎都很好，直到查询更新的记录数开始变得比我在测试数据库中拥有的记录数大。
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.
一旦记录集增长到某个随机大小，大约 500 行，然后我开始收到错误“ORA-19025：EXTRACTVALUE 仅返回一个节点的值”。确定数据库中有一些奇怪的行没有 XPath 的唯一结果，我试图限制查询中的记录以隔离坏记录。但是我一缩小记录集，错误就消失了。对于此 XPath 查询，实际上没有一行会返回多个值。看起来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.
有人对这个有了解吗？我不是 SQL 专家，即便如此，我在 SQL Server 上花费的时间也比 Oracle 多。所以如果我不能让这个工作，我想我会用光标或其他东西做一些乱七八糟的事情。
Any advice/help? If it helps, we're running 10g on the server. Thanks!
任何建议/帮助？如果有帮助，我们将在服务器上运行 10g。谢谢！
回答by Steve Broberg
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.
几乎可以肯定，源 XML 表中至少有一行具有无效值。重写查询以尝试找到它。
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):
例如，使用 XMLPath 谓词可以为您提供第二个位置的节点（我目前无法访问带有 XML 对象的数据库，因此我不能保证以下内容在语法上是完美的）：
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;
