Solved

modify xml with variables

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to automate & schedule this Index optimization Script ? 10 76
SQL HELP 2 82
Sql Permission 6 52
Anyway to make these 2 SQL statements into one? 13 25
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now