如何将XML格式SQL脚本存入变量/表并在XML列检索指定字符串

存入XML变量 #

如果已经拿到监控到的XML处理指令字符串（比如<?sql DROP DATABASE ENCRYPTION KEY; ?>这种格式），直接声明XML变量赋值就行：

DECLARE @xmlVar XML;
SET @xmlVar = '<?sql DROP DATABASE ENCRYPTION KEY; ?>';

要是字符串是从监控日志表这类数据源读出来的，先转成XML类型再赋值：

DECLARE @rawStr NVARCHAR(MAX);
-- 替换成你的监控数据查询逻辑
SET @rawStr = (SELECT MonitoringContent FROM ServerMonitor WHERE LogID = 1);
DECLARE @xmlVar XML;
SET @xmlVar = CAST(@rawStr AS XML);

存入临时表#radhe #

先建带XML列的临时表，再插数据：

-- 创建临时表
CREATE TABLE #radhe (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    SqlScriptXML XML
);

-- 插入单条XML数据
INSERT INTO #radhe (SqlScriptXML)
VALUES ('<?sql DROP DATABASE ENCRYPTION KEY; ?>');

-- 批量插入监控到的所有XML格式脚本
INSERT INTO #radhe (SqlScriptXML)
SELECT CAST(MonitoringContent AS XML)
FROM ServerMonitor
WHERE MonitoringContent LIKE '<?sql%?>';

先声明变量@str，然后用两种方法实现检索：

方法1：用XML的exist()方法（推荐） #

通过XQuery定位XML处理指令节点，再检查内容是否包含目标字符串：

DECLARE @str NVARCHAR(MAX) = 'DROP DATABASE ENCRYPTION KEY';

SELECT *
FROM #radhe
WHERE SqlScriptXML.exist('//processing-instruction("sql")[contains(., sql:variable("@str"))]') = 1;

这里processing-instruction("sql")专门定位XML里的SQL处理指令节点，contains(., sql:variable("@str"))用来匹配变量里的字符串。

方法2：转成字符串后用LIKE匹配 #

如果XML结构简单，直接转成字符串用LIKE也能实现：

DECLARE @str NVARCHAR(MAX) = 'DROP DATABASE ENCRYPTION KEY';

SELECT *
FROM #radhe
WHERE CAST(SqlScriptXML AS NVARCHAR(MAX)) LIKE '%' + @str + '%';

注意：如果XML里有转义字符，这种方法可能匹配不准，适合结构简单的场景。

内容的提问来源于stack exchange，提问作者Marcello Miorelli