在 SQL Server XML 处理中为 modify() 参数化 XPath

2021-10-01 00:00:00 xml xpath sql-server

正如标题所暗示的那样，我正在尝试为 SQL Server 中 XML 数据列的 modify() 方法参数化 XPath，但遇到了一些问题.

Just like the title suggests, I'm trying to parameterize the XPath for a modify() method for an XML data column in SQL Server, but running into some problems.

到目前为止我有:

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 结构:

with the following XML Structure: 

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

然后产生这个错误:

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

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

Now I realize that the modify method must not be capable of accepting a string as a path, but is there a way to accomplish this short of using dynamic SQL?

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

Oh, by the way, I'm using SQL Server 2008 Standard 64-bit, but any queries I write need to be compatible back to 2005 Standard.

谢谢！

推荐答案

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

In case anyone was interested, I came up with a pretty decent solution myself using a dynamic query:

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，就我而言，它完全由我的代码控制，因此不应被利用.

Using this, the only unsafe part of the dynamic query is the xPath, which, in my case, is controlled entirely by my code and so shouldn't be exploitable.

相关文章