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='
<Root>
<Device>
    <Inspection>
        <Status>OK</Status>
    </Inspection>
</Device>
</Root>' 

SELECT @XML1.query('/Root[1]/Device[1]/Inspection[1]/Status[1]')  
SELECT @XML1.value('/Root[1]/Device[1]/Inspection[1]/Status[1]','varchar(5)')  


SET @XPath = '/Root[1]/Device[1]/Inspection[1]/Status[1]' 
SET @EncodedXPath = REPLACE(@XPath, '''', '''''')  

SET @Sql = N'SELECT @XML1.query(''' + @EncodedXPath + N''')'  
EXEC sp_executesql @Sql, N'@XML1 xml', @XML1  

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

if you execute the code above you get same result, but how can i assign the result of the dynamic sql to a variable using xpath? the below example return just the result of the execution but i want to get back the value 'OK'

EXEC @ret = sp_executesql @Sql, N'@XML1 xml', @XML1 

回答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