使用xpath保存exec sp_excutequery的结果 [英] save result of exec sp_excutequery using xpath

问题描述

我有这个例子

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 

如果执行上面的代码，则会得到相同的结果，但是如何使用xpath将动态sql的结果分配给变量？下面的示例仅返回执行结果，但我想取回值'OK'

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 

推荐答案

该方法应该与将任何 sp_executesql 的结果转换为变量相同，无论 sp_executesql 包含XPath还是只是普通的SQL。

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.

这是一种可能的方式，也在 如何将sp_executesql结果转换为变量？ 。修改动态SQL，使其具有用于存储XPath查询结果的输出变量。然后可以从输出变量值中设置静态变量-在 sp_executesql 外部声明的变量：

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

这篇关于使用xpath保存exec sp_excutequery的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！