Save Result Of Exec Sp_excutequery Using Xpath

Hi i have this example DECLARE @XML1 xml, @XPath nvarchar(200), @EncodedXPath nvarchar(200), @Sql nvarchar(max), @val nvarchar(20) SET @XML1='

Solution 1:

The approach should be the same as getting result of any sp_executesql into a variable, no matter the sp_executesql contains XPath or just plain SQL.

This is one possible way, which also suggested in "How to get sp_executesql result into a variable?". Modify your dynamic SQL to have an output variable for storing the result of the XPath query. Then you can set your static variable -the one declared outside sp_executesql- from the output variable value :

....
SET@Sql= N'SET @ret = @XML1.value('''+@EncodedXPath+ N''', ''varchar(5)'')'EXEC sp_executesql @Sql, N'@XML1 xml, @ret varchar(5) output', @XML1, @ret=@ret output

Share