SQL Server 2008存储过程中XQuery无法插入数据如何解决

算力阿峰

2026-8-28

运行环境

Microsoft SQL Server 2008 - Version 10.0.6556.0

问题描述

编写的XML解析插入逻辑代码如下：

;WITH dataAsXml AS
(   
   SELECT
     xmlData.myElement.value('@myAttribute1', 'varchar(10)') AS 'myAttribute1',
     xmlData.myElement.value('@myAttribute2', 'char(5)') AS 'myAttribute2'       
   FROM 
     @input.nodes('/myRootElement/childElement') AS xmlData(myElement)
)
INSERT INTO myTable (myCol1, myCol2)
SELECT myAttribute1, myAttribute2 FROM dataAsXml

上述XQuery逻辑封装到存储过程后无法正常生效：已确认传入的XML源数据存在，但执行存储过程后myTable表无对应数据插入；同一段代码在SSMS查询窗口中可正常运行，仅封装为存储过程后无预期执行结果。
测试使用的XML结构如下：

<?xml version="1.0" encoding="UTF-8"?>
<myRootElement>
  <childElement myAttribute1="Value1" myAttribute2="value2"/>
</myRootElement>

故障原因

这是SQL Server 2008的典型兼容性坑，核心原因是编码隐式转换+参数类型定义错误：

SQL Server 2008没有原生UTF-8编码支持，仅能在XML、NVARCHAR这类Unicode类型中正确识别带encoding="UTF-8"声明的XML内容。
在SSMS查询窗口直接执行代码时，字符串变量会默认按Unicode类型处理，隐式转XML的过程不会出现编码识别错误，XPath可以正常匹配节点。
封装为存储过程后，如果入参@input被定义为VARCHAR类型（尤其是非max长度的VARCHAR），传入的XML内容会按非Unicode编码解析，要么因为编码不匹配导致DOM结构识别异常、XPath匹配不到节点，要么因为长度不足被截断，最终查询返回空结果，无数据插入。

修复方案

按优先级依次排查修复：

优先将存储过程的入参@input直接定义为XML类型，从根源上避免字符串转XML的隐式编码转换问题，参考存储过程定义：

CREATE PROCEDURE dbo.usp_InsertFromXml
  @input XML -- 直接用XML类型接收参数，不要用VARCHAR系列类型
AS
BEGIN
  SET NOCOUNT ON;
  ;WITH dataAsXml AS
  (   
     SELECT
       xmlData.myElement.value('@myAttribute1', 'varchar(10)') AS myAttribute1,
       xmlData.myElement.value('@myAttribute2', 'char(5)') AS myAttribute2       
     FROM 
       @input.nodes('/myRootElement/childElement') AS xmlData(myElement)
  )
  INSERT INTO myTable (myCol1, myCol2)
  SELECT myAttribute1, myAttribute2 FROM dataAsXml 
END

如果受历史逻辑限制必须用字符串类型接收XML内容，必须将参数定义为NVARCHAR(MAX)类型，必要时可以提前去掉XML头的UTF-8声明再做显式转换，避免编码识别错误：

-- 假设入参@inputStr为NVARCHAR(MAX)类型的XML字符串
DECLARE @input XML = CAST(REPLACE(@inputStr, 'encoding="UTF-8"', '') AS XML)
-- 后续用@input执行原有的解析插入逻辑即可

调试阶段可以在存储过程开头临时加SELECT @input输出实际接收到的参数内容，确认XML没有被截断、结构和测试用的内容完全一致，排除参数长度不足导致的内容缺失问题。

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