OPENJSON读取含双引号字段JSON失败，粘贴记事本后恢复正常的原因

问题根源 #

通过xp_cmdshell执行curl时，返回结果按行拆分，每行末尾可能携带**回车符(CR, CHAR(13))或换行符(LF, CHAR(10))**这类隐藏控制字符。直接拼接这些行到NVARCHAR变量时，控制字符会破坏JSON格式完整性，导致ISJSON校验失败、OPENJSON解析报错。而复制到记事本再粘贴时，记事本会自动清理多余控制字符，所以能正常解析。

低开销解决方案（无需存文件） #

通过临时表存储curl返回的每一行，拼接时统一清理控制字符，再合并为完整的JSON字符串。

具体实现代码 #

DECLARE @InJson NVARCHAR(MAX) = '';
DECLARE @curlCmd NVARCHAR(4000) = 'curl -s "https://your-algolia-index-url"'; -- 替换为你的Algolia请求地址

-- 创建临时表存储curl返回的每行结果
CREATE TABLE #CurlResults (LineContent NVARCHAR(MAX));

-- 执行curl并将结果插入临时表
INSERT INTO #CurlResults
EXEC xp_cmdshell @curlCmd;

-- 拼接时清理控制字符（CR/LF），过滤NULL行（xp_cmdshell可能返回空行）
SELECT @InJson += REPLACE(REPLACE(LineContent, CHAR(13), ''), CHAR(10), '')
FROM #CurlResults
WHERE LineContent IS NOT NULL;

-- 验证JSON有效性并解析
IF ISJSON(@InJson) = 1
BEGIN
    SELECT * FROM OPENJSON(@InJson) WITH (
        -- 替换为你的JSON结构字段定义
        Id INT '$.id',
        Title NVARCHAR(200) '$.title',
        Content NVARCHAR(MAX) '$.content'
    );
END
ELSE
BEGIN
    PRINT 'JSON格式无效';
END

-- 清理临时表
DROP TABLE #CurlResults;

关键处理点 #

• 用临时表接收xp_cmdshell返回的所有行，过滤掉NULL行（curl执行可能产生空行）
• 拼接时通过REPLACE函数移除回车符和换行符，避免控制字符破坏JSON结构
• 最终得到的@InJson为干净的JSON字符串，可直接用OPENJSON解析

内容的提问来源于stack exchange，提问作者George Narr