调用MercadoLibre API后SQL Server无法用OPENJSON解析JSON响应

核心问题 #

1. 编码不匹配：MercadoLibre API返回的是UTF-8编码的JSON，而MSXML2.XMLHTTP的responseText在SQL Server中默认以ANSI编码（对应VARCHAR类型）解析，导致西班牙语特殊字符（如ñ、á等）出现乱码，破坏JSON语法结构。
2. 变量截断：你定义的@respuesta是VARCHAR(8000)，但API返回的JSON内容长度很可能超过8000字符，赋值时会被截断，导致JSON不完整。

而将响应保存为TXT文件后用OPENROWSET能正常解析，是因为文件保存时保留了UTF-8编码，OPENROWSET读取时会正确识别编码，避免了转换错误。

修复方案 #

方案1：修正变量类型并指定请求编码 #

直接将存储响应的变量改为NVARCHAR(MAX)，同时添加请求头强制API返回UTF-8编码内容：

SET TEXTSIZE 2147483647
DECLARE @url VARCHAR(8000) = 'https://api.mercadolibre.com/products/MLA18648076'
DECLARE @token as int;
DECLARE @respuesta as NVARCHAR(MAX); -- 改为NVARCHAR(MAX)避免截断和编码问题
DECLARE @ret INT;
DECLARE @respuesTabla table(respuestaTxt nvarchar(max))

EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
-- 添加请求头指定接受UTF-8编码
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Accept-Charset', 'utf-8';
EXEC @ret = sp_OAMethod @token, 'send'
insert into @respuesTabla (respuestaTxt)
EXEC @ret = sp_OAMethod @token, 'responseText'
select @respuesta = respuestaTxt from @respuesTabla

-- 现在可正常使用OPENJSON解析
SELECT * FROM OPENJSON(@respuesta)

EXEC sp_OADestroy @token

方案2：通过字节流处理避免编码转换错误 #

使用ADODB.Stream读取API返回的字节流，直接转换为UTF-8编码的NVARCHAR，彻底解决编码问题：

SET TEXTSIZE 2147483647
DECLARE @url VARCHAR(8000) = 'https://api.mercadolibre.com/products/MLA18648076'
DECLARE @token int, @adostream int
DECLARE @respuesta NVARCHAR(MAX)
DECLARE @ret INT

-- 创建XMLHTTP请求对象
EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT
IF @ret <> 0 RAISERROR('Failed to create XMLHTTP object.', 10, 1)

EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false'
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Accept', 'application/json'
EXEC @ret = sp_OAMethod @token, 'send'

-- 创建ADODB.Stream处理字节流
EXEC @ret = sp_OACreate 'ADODB.Stream', @adostream OUT
IF @ret <> 0 RAISERROR('Failed to create ADODB.Stream object.', 10, 1)

EXEC @ret = sp_OAMethod @adostream, 'Open'
EXEC @ret = sp_OAMethod @adostream, 'Write', NULL, @token.responseBody
EXEC @ret = sp_OAMethod @adostream, 'Position', NULL, 0
EXEC @ret = sp_OAMethod @adostream, 'Charset', NULL, 'utf-8'
EXEC @ret = sp_OAMethod @adostream, 'ReadText', @respuesta OUT

-- 解析JSON内容
SELECT * FROM OPENJSON(@respuesta)

-- 清理COM对象
EXEC sp_OADestroy @adostream
EXEC sp_OADestroy @token

内容的提问来源于stack exchange，提问作者Juan Pablo Humani