Menu
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?
515
Querying in an XQuery contextIf your query invokes an XQuery expression directly, you must prefix it with the case-insensitive keyword XQUERY. To retrieve all of the XML documents previously inserted into the INFO column, you can use XQuery with either db2-fn:xmlcolumn or db2-fn:sqlquery.
You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.
SQL Server provides the XQuery feature to querying XML data type or querying with the XML column with the XPATH. Using XQuery, users can Insert, Update and Delete with the XML nodes and node values in an XML column.
To retrieve data in XML format from SQL Server database, we can use FOR XML <options> clause. Notice the last three words in the above query. We have a normal SELECT statement and in the last of the statement, we are suffixing FOR XML RAW that will return the data from PersonalDetails table in Raw xml format.
Try something like
ISNULL(@Xml.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/@*[local-name() = sql:variable("@Attribute")])[1]','varchar(max)'),'')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
