How to get value from XML attribute using Sql:Variable in xquery

Question

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?

What version of SQL Server? If you're using .value, has to be 2005+ — OMG Ponies
– OMG Ponies, Commented Jul 27, 2012 at 4:25

Adriaan Stander · Accepted Answer · 2012-07-27 04:56:30Z

2

Try something like

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

answered Jul 27, 2012 at 4:56

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get value from XML attribute using Sql:Variable in xquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related