SQL Server中image字段存储XML的查询与提取编辑方案咨询

直接CAST失败大概率是因为image字段存储的是二进制格式，需要先转成字符串再转XML，试试下面几种方法：

• 基础转换方法
  如果XML是UTF-8编码，用这个查询提取：

  SELECT 
      CONVERT(XML, CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), YourImageColumn)))
  FROM YourTable;
  

  如果是UTF-16编码，换成NVARCHAR转换：

  SELECT 
      CONVERT(XML, CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), YourImageColumn)))
  FROM YourTable;
  

• 处理带BOM的情况
  要是XML开头有UTF-16的BOM（字节顺序标记），直接转换会报错，需要截取掉前2个字节：

  SELECT 
      CONVERT(XML, CONVERT(NVARCHAR(MAX), SUBSTRING(CONVERT(VARBINARY(MAX), YourImageColumn), 3, 2147483647)))
  FROM YourTable;
  

• 提取后的编辑与回存
  把查询到的XML内容复制到VS Code、Notepad++这类支持XML语法的编辑器里直接编辑。编辑完成后，用下面的语句把修改后的XML存回image字段：

  UPDATE YourTable
  SET YourImageColumn = CONVERT(VARBINARY(MAX), N'编辑后的完整XML内容')
  WHERE 你的筛选条件;
  

注意：如果不确定编码，可以两种转换方式都试一遍；要是二进制里还有其他冗余字节，得先确认冗余部分的长度再调整截取位置。

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