modify xml with variables
I want to make a function that modifys a xml string. the problem is using variables in the modify expression. @key has the where clause of the modify. I get the error: The argument 1 of the xml data type method "modify" must be a string literal.
ALTER FUNCTION dbo.XmlUpdate (
@str nvarchar(max),
@key nvarchar(50),
@value nvarchar(100)
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @xml XML
SET @xml = convert(XML, @str)
SET @xml.modify('replace value of (' + @key + ')[1] with sql:variable("@value")')
SET @str = Convert(nvarchar(max), @xml)
RETURN @str
END
Function CALL:
dbo.XmlUpdate(SettingValue, '/FilterParametersList/Filters/Filter/Parameters/Parameter[@id="asdf"]/Name/text()', '1234')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can see an example of how to use sp_executesql with variables in sql server books online