问题描述
我有一个以文本形式存储的类型化 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 *' 的操作数
我在谷歌上搜索到的内容是 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!