如何使用Sql获取XML属性的值：xquery中的变量

I want to get attribute value from XML using Xquery.

我想使用Xquery从XML获取属性值。

MY XML is

我的XML是

<Answers>
  <AnswerSet>
    <Answer questionId="NodeID">155</Answer>
    <Answer questionId="ParentNode" selectedValue="12">Product</Answer>
  </AnswerSet>
</Answers>

Below is my query.

以下是我的查询。

DECLARE @Field Varchar(100)
DECLARE @Attribute VARCHAR(100)
SET @Field='ParentNode'
SET @Attribute = 'selectedValue'

SELECT ISNULL(PropertyXML.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")])[1]','varchar(max)'),'') ,

ISNULL(PropertyXML.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/sql:variable(@Attribute) )[1]','varchar(max)'),'') FROM node WHERE id=155

ISNULL(PropertyXML.value('(/ Answers / AnswerSet / Answer [@ questionId = sql:variable(“@ Field”)] / sql:variable(@Attribute))[1]','varchar(max)'), '')FROM节点WHERE id = 155

below line is working fine with sql:variable

下面的行与sql:variable一起工作正常

ISNULL(PropertyXML.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")])[1]','varchar(max)'),'')

but I am getting error in below line..

但我在下面的行中得到错误..

ISNULL(PropertyXML.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/sql:variable(@Attribute) )[1]','varchar(max)'),'')

I want to get provided attribute(@Attribute) value in result.

我想在结果中获得提供的属性(@Attribute)值。

1 个解决方案

#1

Try something like

尝试类似的东西

ISNULL(@Xml.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/@*[local-name() = sql:variable("@Attribute")])[1]','varchar(max)'),'')