We help IT Professionals succeed at work.

modify xml with variables

1eEurope
1eEurope asked
on
Medium Priority
892 Views
Last Modified: 2012-08-13
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')

Open in new window

Comment
Watch Question

i think you can't use a variable, you will need to use sp_executesql with the modify statement and link the variables
you can see an example of how to use sp_executesql with variables in sql server books online
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.