在 SQL Server XML 处理中参数化 XPath 以便修改()

Question

正如标题所示，我尝试参数化 SQL Server 中 XML 数据列的 edit() 方法的 XPath，但遇到了一些问题。

到目前为止，我有：

DECLARE @newVal varchar(50)
DECLARE @xmlQuery varchar(50)
SELECT @newVal = 'features'
SELECT @xmlQuery = 'settings/resources/type/text()'

UPDATE  [dbo].[Users]
SET     [SettingsXml].modify('
    replace value of (sql:variable("@xmlQuery"))[1]
    with sql:variable("@newVal")')
WHERE   UserId = 1

具有以下 XML 结构：

<settings>
    ...
    <resources>
        <type> ... </type>
        ...
    </resources>
    ...
</settings>

然后生成此错误：

XQuery [dbo.Users.NewSettingsXml.modify()]: 'replace' 的目标最多只能是一个节点，找到 'xs :string ?'

现在我意识到修改方法一定不能接受字符串作为路径，但是有没有办法使用动态 SQL 来完成这个缺点呢？

哦，顺便说一下，我使用的是 SQL Server 2008 Standard 64 位，但我编写的任何查询都需要兼容回 2005 Standard。

谢谢！

Answer 1

如果有人感兴趣，我自己使用动态查询想出了一个相当不错的解决方案：

DECLARE @newVal nvarchar(max)
DECLARE @xmlQuery nvarchar(max)
DECLARE @id int

SET @newVal = 'foo'
SET @xmlQuery = '/root/node/leaf/text()'
SET @id = 1

DECLARE @query nvarchar(max)

SET @query = '
    UPDATE  [Table]
    SET     [XmlColumn].modify(''
        replace value of (' + @xmlQuery + '))[1]
        with sql:variable("@newVal")'')
    WHERE Id = @id'

EXEC sp_executesql @query,
                   N'@newVal nvarchar(max) @id int',
                   @newVal, @id

使用这个，动态查询的唯一不安全部分是 xPath，在我的情况下，它完全由我的代码控制，所以应该不被利用。

Answer 2

我能想到的最好的办法是：

declare @Q1 varchar(50)
declare @Q2 varchar(50)
declare @Q3 varchar(50)
set @Q1 = 'settings'
set @Q2 = 'resources'
set @Q3 = 'type'

UPDATE  [dbo].[Users]
SET     [SettingsXml].modify('
    replace value of (for $n1 in /*,
                          $n2 in $n1/*,
                          $n3 in $n2/*
                      where $n1[local-name(.) = sql:variable("@Q1")] and
                            $n2[local-name(.) = sql:variable("@Q2")] and
                            $n3[local-name(.) = sql:variable("@Q3")]
                      return $n3/text())[1]
    with sql:variable("@newVal")')
WHERE   UserId = 1

节点名称是参数，但遗憾的是节点的级别/数量不是。

Answer 3

这是我们找到的用于参数化要替换的属性名称和新值的解决方案。它需要一个特定的xpath，参数名称可以是sql变量或表列。

SET Bundle.modify
(
  'replace value of(//config-entry-metadata/parameter-name[text() = sql:column("BTC.Name")]/../..//value/text())[1] with sql:column("BTC.Value") '
)

这是硬编码的 x 路径： //config-entry-metadata/parameter-name ... /../..//value/text()
参数名称是动态的：[text() = sql:column("BTC.Name")]
新值也是动态的：with sql:column("BTC.Value")