6

I want to get attribute value from XML using Xquery.

MY XML is

<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

below line is working fine with 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)'),'')

Any ideas on how to get provided attribute(@Attribute) value in result?

TAbdiukov
  • 1,185
  • 3
  • 12
  • 25
Mohmedsadiq
  • 133
  • 1
  • 10

1 Answers1

2

Try something like

ISNULL(@Xml.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/@*[local-name() = sql:variable("@Attribute")])[1]','varchar(max)'),'')
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284