問題描述
正如標題所暗示的那樣，我正在嘗試為 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.
這篇關于在 SQL Server XML 處理中為 modify() 參數化 XPath的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！