XML query() 有效，value() 需要找到单例 xdt:untypedAtomic [英] XML query() works, value() requires singleton found xdt:untypedAtomic

问题描述

我有一个以文本形式存储的类型化 xml 文档.因此，我使用通用表表达式将数据类型转换为 xml，以便能够使用 XML 方法:

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:

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

这给了我以下错误:

'value()' 需要一个单例(或空序列)，找到类型为 'xdt:untypedAtomic *' 的操作数

'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

我在谷歌上搜索到的内容是 XPATH/XQUERY 需要在括号内和/或需要[1]" - 两者都不起作用.xml 中只有一个 student-id 元素，但我想架构允许更多?

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?

此外，我想检索许多元素值 - 有没有办法声明命名空间一次而不是每个方法调用?

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?

推荐答案

你需要使用这个:

SELECT 
        x.requestpayload.value('declare namespace s="http://blah.ca/api";
            (/s:validate-student-request/s:student-id)[1]', 'int') 
    AS
        studentid
    FROM 
        xoutput x

您需要将 XPath 放入 ( ... ) 并添加 [1] 以简单地选择该序列的第一个值.

You need to put your XPath in ( ... ) and add a [1] to simply select the first value of that sequence.

这篇关于XML query() 有效，value() 需要找到单例 xdt:untypedAtomic的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！