此查询返回属性值"storedId1">
SET @xml = N'<Data>
<Ref ID="1" sf="storedId1">
this is the value I want
</Ref>
</Data>'
SELECT
T.C.value('@sf', 'nvarchar(MAX)') AS result
FROM
@xml.nodes('Data/Ref') T(C)
WHERE
T.C.value('@sf', 'nvarchar(MAX)') = 'storedId1'
如何返回节点值,即返回"这是我想要的值">
我想我需要将 SELECT 更改为类似的东西
SELECT T.C.value('Data/Ref', 'nvarchar(MAX)') AS result
但它失败了
XQuery [value((]: 'value((' 需要一个单例(或空序列(,找到类型为 'xdt:untypedAtomic *' 的操作数
试试这个:
SELECT
T.C.value('(./text())[1]', 'nvarchar(MAX)') AS result
FROM
@xml.nodes('Data/Ref[@sf="storedId1"]') T(C)
DECLARE @xml xml
SET @xml = N'<Data>
<Ref ID="1" sf="storedId1">
this is the value I want
</Ref>
</Data>'
SELECT
T.C.value('.', 'nvarchar(MAX)') AS result
FROM
@xml.nodes('Data/Ref') T(C)
WHERE
T.C.value('@sf', 'nvarchar(MAX)') = 'storedId1'
演示:http://sqlfiddle.com/#!18/a7540/28512