Solved

modify xml with variables

Posted on 2009-07-07
2
324 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

0
Comment
Question by:1eEurope
2 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24800579
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
0
 
LVL 1

Accepted Solution

by:
1eEurope earned 0 total points
ID: 24801729
you cant use sp_executesql in a function
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question