如何从SSRS ExecutionLog3视图解析参数并按指定格式输出？

你需要从ReportServer的ExecutionLog3视图参数列中，提取固定的Album、RecordingDate、ReleaseDate、AlbumName字段，同时捕获部分记录中存在的额外参数（如ArtistId、SurroundAvailable），解决原代码因参数顺序不固定导致的提取错误问题。以下是可行的SQL实现：

DECLARE @t TABLE (
  c_parameters VARCHAR(1000)
) 
INSERT @t 
VALUES 
('login=res%5Cel&Album=293&RecordingDate=11%2F03%2F2012%2000%3A00%3A00&ReleaseDate=11%2F12%2F2013%2000%3A00%3A00&ArtistId=404&AlbumName=MidnightDreamscapes'),
('login=res%5Cel&RecordingDate=12%2F05%2F2007%2000%3A00%3A00&ReleaseDate=10%2F15%2F2008%2000%3A00%3A00&Album=580&AlbumName=EtherealHorizons'),
('login=res%5Cel&Album=403&RecordingDate=1%2F01%2F2009%2000%3A00%3A00&ReleaseDate=9%2F18%2F2015%2000%3A00%3A00&SurroundAvailable=1&AlbumName=ElectricOceans'),
('login=res%5Cel&Album=676&RecordingDate=8%2F09%2F2011%2000%3A00%3A00&ReleaseDate=12%2F25%2F2012%2000%3A00%3A00&AlbumName=CosmicEchoes')

-- 先将参数拆分为键值对列表
;WITH ParamPairs AS (
    SELECT 
        c_parameters,
        CAST('<param>' + REPLACE(REPLACE(c_parameters, '&', '</param><param>'), '=', '</key><value>') + '</param>' AS XML) AS ParamXML
    FROM @t
),
-- 提取所有键值对
KeyValues AS (
    SELECT 
        c_parameters,
        Param.value('key[1]', 'VARCHAR(200)') AS ParamName,
        Param.value('value[1]', 'VARCHAR(200)') AS ParamValue
    FROM ParamPairs
    CROSS APPLY ParamXML.nodes('/param') AS P(Param)
)
-- 聚合固定字段并匹配额外参数
SELECT
    MAX(CASE WHEN ParamName = 'RecordingDate' THEN ParamValue END) AS RecordingDate,
    MAX(CASE WHEN ParamName = 'ReleaseDate' THEN ParamValue END) AS ReleaseDate,
    MAX(CASE WHEN ParamName = 'Album' THEN ParamValue END) AS Album,
    MAX(CASE WHEN ParamName = 'AlbumName' THEN ParamValue END) AS AlbumName,
    -- 提取非固定的额外参数（仅保留一个，若有多个可调整逻辑）
    MAX(CASE WHEN ParamName NOT IN ('login', 'RecordingDate', 'ReleaseDate', 'Album', 'AlbumName') THEN ParamName END) AS ParamName,
    MAX(CASE WHEN ParamName NOT IN ('login', 'RecordingDate', 'ReleaseDate', 'Album', 'AlbumName') THEN ParamValue END) AS ParamValue
FROM KeyValues
GROUP BY c_parameters
ORDER BY Album;

实现思路 #

• 参数拆分：将参数字符串转换为XML格式，把每个key=value对拆分为<param><key>...</key><value>...</value></param>节点，彻底解决参数顺序不固定的问题。
• 键值对提取：通过CROSS APPLY和nodes()方法解析XML，得到每条记录的所有参数键值对，为后续匹配提供基础。
• 固定字段聚合：使用CASE语句结合MAX函数，按参数名称精准提取固定字段的值，确保无论参数顺序如何都能正确匹配。
• 额外参数捕获：筛选出非固定的参数（排除login和固定字段），提取对应的参数名和值；若单条记录存在多个额外参数，可根据需求调整聚合逻辑（如合并为字符串或拆分多行）。

内容的提问来源于stack exchange，提问作者Lionel Hamayon