I want to extract value from XML using XML value function. The path of the value, in this case, is stored in a variable. Check below script
DECLARE @keyPathNg NVARCHAR(max)
DECLARE @xml_col XML
SET @keyPathNg = '/note/to/text()'
SET @xml_col = '<note><to>Tove</to><from>Jani</from><heading>Reminder</heading><body>Dont forghttps://stackoverflow.com/questions/10408445/the-argument-1-of-the-xml-data-type-method-value-must-be-a-string-literalet me this weekend!</body></note>'
select @xml_col.value('(sql:variable("@keyPathNg"))[1]','nvarchar(max)') ;
It's giving the output:
/note/to/text()
However, the output should be Tove
I am successfully able to extract the value when using a hardcoded path instead of the variable using the below query.
select @xml_col.value('(/note/to/text())[1]','varchar(max)')
As mentioned, in my case path is stored in a variable, please suggest.
I have read this answer on stack overflow The argument 1 of the XML data type method "value" must be a string literal, but as checked I am not getting the value of the variable as output, maybe I am missing some thing.
select @xml_col.value('(local-name() = sql:variable("@keyPathNg"))[1]','nvarchar(max)')gettingSQL Error [2371] [S0001]: XQuery [value()]: 'local-name()' can only be used within a predicate or XPath selectorerror. Please suggest.select @xml_col.value('(sql:variable("@keyPathNg"))[1]','nvarchar(max)') ;this should work. I am trying to work on dynamic sql nowsql:variableis a value. It is not a macro, so after the substitution the string is not xpath-parsed again. Definetly the case for dynamic sql.