SQL Server 2012中用@xml.modify插入带变量属性的XML元素

问题 #

需要通过变量向XML中添加带id和nPages属性的<ImageInfo>元素，固定值写法可行：

declare @XML xml = '
<DWDocument>
  <FileInfos>
    <ImageInfos>
      <ImageInfo id="0,0,0" nPages="0">
        <FileInfo fileName="9b7b36ac-c705-49ba-bf91-a952bdb44576.eml" dwFileName="f0.eml" type="normal" length="0" />
      </ImageInfo>
      <ImageInfo id="1,0,0" nPages="0">
        <FileInfo fileName="Response to Deficiency Letter.docx" dwFileName="f1.docx" type="normal" length="0" />
      </ImageInfo>
    </ImageInfos>
  </FileInfos>
</DWDocument>
'

SET @xml.modify ('insert <ImageInfo id="2,0,0" nPages="0"></ImageInfo>
         as last into (DWDocument/FileInfos/ImageInfos)[1]')

但尝试用变量设置属性时出现多种问题：

• 写法1报错Msg 2225, Level 16, State 1, Line 46 XQuery [modify()]: A string literal was expected：

  declare @XML xml = '
  <DWDocument>
    <FileInfos>
      <ImageInfos>
        <ImageInfo id="0,0,0" nPages="0">
          <FileInfo fileName="9b7b36ac-c705-49ba-bf91-a952bdb44576.eml" dwFileName="f0.eml" type="normal" length="0" />
        </ImageInfo>
        <ImageInfo id="1,0,0" nPages="0">
          <FileInfo fileName="Response to Deficiency Letter.docx" dwFileName="f1.docx" type="normal" length="0" />
        </ImageInfo>
      </ImageInfos>
    </FileInfos>
  </DWDocument>
  '
  
  declare @ImageInfo_ID nvarchar(50) = '"2,0,0"'
  declare @ImageInfo_nPages nvarchar(50) = '"0"'
  
  SET @xml.modify ('insert <ImageInfo id={sql:variable("@ImageInfo_ID")}></ImageInfo>
           as last into (DWDocument/FileInfos/ImageInfos)[1]')
  

• 写法2报错Msg 2205, Level 16, State 1, Line 48 XQuery [modify()]: "=" was expected.：

  SET @xml.modify ('insert <ImageInfo id{sql:variable("@ImageInfo_ID")}></ImageInfo>
           as last into (DWDocument/FileInfos/ImageInfos)[1]')
  

• 写法3无报错但结果错误，属性变成元素内容：

  SET @xml.modify ('insert <ImageInfo>{sql:variable("@ImageInfo_ID")}</ImageInfo>
           as last into (DWDocument/FileInfos/ImageInfos)[1]')
  

  得到错误结果：

  <ImageInfo>"2,0,0" nPages="0"</ImageInfo>
  

目标是通过变量生成：

<ImageInfo id="2,0,0" nPages="0"></ImageInfo>

正确写法 #

核心要点：

1. 变量存储纯属性值，不要包含引号；
2. 在XQuery的属性定义中，保留属性的引号，将变量嵌入引号内部。

完整代码示例：

declare @XML xml = '
<DWDocument>
  <FileInfos>
    <ImageInfos>
      <ImageInfo id="0,0,0" nPages="0">
        <FileInfo fileName="9b7b36ac-c705-49ba-bf91-a952bdb44576.eml" dwFileName="f0.eml" type="normal" length="0" />
      </ImageInfo>
      <ImageInfo id="1,0,0" nPages="0">
        <FileInfo fileName="Response to Deficiency Letter.docx" dwFileName="f1.docx" type="normal" length="0" />
      </ImageInfo>
    </ImageInfos>
  </FileInfos>
</DWDocument>
'

-- 变量仅存属性值，不带引号
declare @ImageInfo_ID nvarchar(50) = '2,0,0'
declare @ImageInfo_nPages nvarchar(50) = '0'

-- 在属性的引号内部嵌入变量
SET @xml.modify ('insert <ImageInfo id="{sql:variable("@ImageInfo_ID")}" nPages="{sql:variable("@ImageInfo_nPages")}"></ImageInfo>
         as last into (DWDocument/FileInfos/ImageInfos)[1]')

-- 验证结果
SELECT @XML

错误原因说明 #

• 写法1：变量包含了引号，导致XQuery解析时把带引号的内容当作整个属性值，破坏了语法结构；
• 写法2：属性名id和变量之间缺少=，不符合XML属性的语法规则；
• 写法3：把变量放到了元素的内容区域，而非属性定义位置，自然无法生成正确的属性。

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