All geek questions in one place

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

I want to get an attribute value from XML using Xquery.

MY XML

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

Below is my request.

 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 works fine with sql: variable

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

but I get the error in the line below.

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

I want to get attribute attribute (@Attribute) as a result.

+6
source share
1 answer

Try something like

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

Source: https://habr.com/ru/post/921449/

More articles:


All Articles