Solved

modify xml with variables

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

11 Experts available now in Live!

Get 1:1 Help Now