?
Solved

modify xml with variables

Posted on 2009-07-07
2
Medium Priority
?
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

800 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