Parameterizing XPath for modify() in SQL Server XML Processing
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.
So far I have:
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
with the following XML Structure:
<settings>
...
<resources>
<type> ... </type>
...
</resources>
...
</settings>
which is then generating this error:
XQu开发者_StackOverflow中文版ery [dbo.Users.NewSettingsXml.modify()]: The target of 'replace' must be at most one node, found 'xs:string ?'
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?
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.
Thanks!
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
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.
The best I could figure out was this:
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
Node names are parameters but the level/number of nodes is sadly not.
Here is the solution we found for parameterizing both the property name to be replaced and the new value. It needs a specific xpath, and the parameter name can be an sql variable or table column.
SET Bundle.modify
(
'replace value of(//config-entry-metadata/parameter-name[text() = sql:column("BTC.Name")]/../..//value/text())[1] with sql:column("BTC.Value") '
)
This is the hard coded x path: //config-entry-metadata/parameter-name ... /../..//value/text()
The name of the parameter is dynamic: [text() = sql:column("BTC.Name")]
The new value is also dynamic: with sql:column("BTC.Value")
精彩评论