SQL XML query() 有效，value() 需要找到单例 xdt:untypedAtomic
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1302064/
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
XML query() works, value() requires singleton found xdt:untypedAtomic
提问by OMG Ponies
I have a typed xml document stored as text. So I use CONVERT the data type to xml by using a Common Table Expression in order to be able to use XML methods:
我有一个存储为文本的类型化 xml 文档。因此，我使用通用表表达式将数据类型转换为 xml，以便能够使用 XML 方法：
WITH xoutput AS (
SELECT CONVERT(xml, t.requestpayload) 'requestpayload'
FROM TABLE t
WHERE t.methodid = 1)
SELECT x.requestpayload.query('declare namespace s="http://blah.ca/api";/s:validate-student-request/s:student-id') as studentid
FROM xoutput x
Query works, returning to me the element. But I'm only interested in the value:
查询有效，将元素返回给我。但我只对价值感兴趣：
WITH xoutput AS (
SELECT CONVERT(xml, t.requestpayload) 'requestpayload'
FROM TABLE t
WHERE t.methodid = 1)
SELECT x.requestpayload.value('declare namespace s="http://blah.ca/api";/s:validate-student-request/s:student-id', 'int') as studentid
FROM xoutput x
This gives me the following error:
这给了我以下错误：
'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
'value()' 需要一个单例（或空序列），找到类型为 'xdt:untypedAtomic *' 的操作数
What I've googled says that the XPATH/XQUERY needs to be inside parenthesis and/or needs "[1]" - neither has worked. There's only one student-id element in the xml, though I guess the schema allows for more?
我用谷歌搜索过的内容说 XPATH/XQUERY 需要在括号内和/或需要“[1]” - 两者都没有奏效。xml 中只有一个 student-id 元素，但我想模式允许更多？
Additionally, there are numerous element values I'd like to retrieve - is there a way to declare the namespace once rather than per method call?
此外，我想检索许多元素值 - 有没有办法声明命名空间一次而不是每个方法调用？
回答by marc_s
You need to use this:
你需要使用这个：
SELECT
x.requestpayload.value('declare namespace s="http://blah.ca/api";
(/s:validate-student-request/s:student-id)[1]', 'int')
AS
studentid
FROM
xoutput xYou need to put your XPath in ( ... )and add a [1]to simply select the first value of that sequence.
您需要放入 XPath( ... )并添加一个[1]以简单地选择该序列的第一个值。
回答by Ken
I believe this might also do:
我相信这也可以：
SELECT
x.requestpayload.query('declare namespace s="http://blah.ca/api";
/s:validate-student-request/s:student-id').value('.', 'int')
as studentid
FROM xoutput x
回答by David Coster
For those interested in performance I ran a query to compare these approaches and the first option with "() and add a [1]" was MUCH faster than ".query('strFranchise').value('.',...)".
对于那些对性能感兴趣的人，我运行了一个查询来比较这些方法，第一个选项与 "() 并添加 [1]" 比 ".query('strFranchise').value('.',... )”。
Difference in Execution plan was 15% to 85% when running one after the other on same data. So ()[1] is over 5 times faster! Execution plan is much different.
在相同数据上一个接一个运行时，执行计划的差异为 15% 到 85%。所以 ()[1] 快了 5 倍以上！执行计划大不相同。
