SQL Server提取动态结构XML数据遇value参数报错的解决方法

现有一张业务数据表，表中每一行存储的XML内容结构存在差异，对应的XML节点字段互不相同。

需要根据每行记录中VsDateField、ProfField、VsKeyField三个字段存储的节点名称取值，从对应行的PATDATA XML字段中提取目标节点数据，不同行对应的目标节点路径取值不固定。

曾尝试通过字符串拼接的方式编写动态XPath表达式读取XML节点内容，实现代码如下：

PATDATA.value('(/rc0004//'+VsDateField+'/node())[1]', 'nvarchar(max)')

代码执行后返回如下错误：

The argument 1 of the XML data type method "value" must be a string literal.

即XML数据类型的value方法要求第一个入参必须为字符串字面量，不支持动态拼接的变量路径。

SQL Server原生XPath不支持直接在路径中拼接变量/字段值，可根据场景选择以下两种实现方式：

• 方案一：节点名匹配（优先推荐，无性能损耗、无注入风险）
  借助XPath通配符和sql:column()函数，直接在表达式中匹配当前行的目标节点名，无需拼接字符串，完全符合value方法的参数要求，示例代码：

  SELECT
      -- 提取VsDateField对应节点值
      PATDATA.value('(/rc0004//*[local-name() = sql:column("VsDateField")]/text())[1]', 'nvarchar(max)') AS TargetDateValue,
      -- 提取ProfField对应节点值
      PATDATA.value('(/rc0004//*[local-name() = sql:column("ProfField")]/text())[1]', 'nvarchar(max)') AS TargetProfValue,
      -- 提取VsKeyField对应节点值
      PATDATA.value('(/rc0004//*[local-name() = sql:column("VsKeyField")]/text())[1]', 'nvarchar(max)') AS TargetKeyValue
  FROM 你的业务表名
  

  逻辑说明：用*匹配路径下任意层级的任意节点，通过local-name()获取节点的本地名称，和当前行存储的目标节点名字段做等值判断，直接命中目标节点提取值。90%以上的动态节点取值场景都可以用该方案实现，性能远高于动态SQL，也不存在注入风险，优先选用。

• 方案二：动态SQL拼接（适配复杂路径场景）
  如果节点路径存在固定层级、特殊命名空间等通配符无法覆盖的场景，可以通过动态SQL拼接出完整的字面量XPath表达式再执行，注意对节点名字段做单引号转义避免SQL注入，示例代码：

  DECLARE @execSql NVARCHAR(MAX)
  -- 批量拼接所有行的查询逻辑
  SELECT @execSql = STRING_AGG(
      N'SELECT ' + CAST(表主键ID AS NVARCHAR(50)) + N' AS RecordID,
      PATDATA.value(''(/rc0004/' + REPLACE(VsDateField, '''', '''''') + '/text())[1]'', ''nvarchar(max)'') AS TargetDateValue,
      PATDATA.value(''(/rc0004/' + REPLACE(ProfField, '''', '''''') + '/text())[1]'', ''nvarchar(max)'') AS TargetProfValue,
      PATDATA.value(''(/rc0004/' + REPLACE(VsKeyField, '''', '''''') + '/text())[1]'', ''nvarchar(max)'') AS TargetKeyValue
      FROM 你的业务表名 WHERE 表主键ID = ' + CAST(表主键ID AS NVARCHAR(50)),
      N' UNION ALL '
  )
  FROM 你的业务表名
  
  -- 执行拼接完成的SQL
  EXEC sp_executesql @execSql
  

内容的提问来源于stack exchange，提问作者Ravit Shagan Damti