How to get value from XML attribute using Sql:Variable in xquery
sql, sql-server, t-sql, xquery-sql
Solution
Try something like
ISNULL(@Xml.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/@*[local-name() = sql:variable("@Attribute")])[1]','varchar(max)'),'')
Problem
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?