RE: How can I replace a value in xml with .modify from the variable?


Thanks for your help.

I am trying to modify a value within an xml. I found that this can be done with .modify but I must use literal for modify command. I need it variablized. Is there any way to do it without sp_executesql?

thanks

declare

@xml varchar(max)

,@xml1 xml

, @ConversationHandle char(36)

set @xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'

SET @xml1 = @xml

SET @ConversationHandle = newid()

This is what I want but using the xml .modify function

select cast(@xml as xml), cast(REPLACE( @xml, 'ConversationHandle=""', 'ConversationHandle="' + @ConversationHandle + '"') as xml)



SET @xml1.modify('

replace value of (/Tasks/row/@ConversationHandle)[1]

with "boo"

')

SELECT @xml1

DECLARE @m varchar(1000)

SET @m ='

replace value of (/Tasks/row/@ConversationHandle)[1]

with "' + cast(@ConversationHandle as char(36)) + '"'

SET @xml1.modify(@m) -- this errors

SELECT @xml1








Thanks for your help.
I am trying to modify a value within an xml. I =
found that=20
this can be done with .modify but I must use literal for modify command. =
I need=20
it variablized. Is there any way to do it without =
sp_executesql?
thanks
declare
@xml varchar(max)
,@xml1 xml
, @ConversationHandle char(36)
set @xml=3D ''
SET @xml1 =3D @xml
SET @ConversationHandle =3D newid()
This is what I want but using the xml .modify=20
function
select cast(@xml as xml), cast(REPLACE(=20
@xml, =
'ConversationHandle=3D""', 'ConversationHandle=3D"' + @ConversationHandle + '"') as xml)

SET @xml1.modify('
replace value of (/Tasks/row/@ConversationHandle)[1]
with "boo"
')
SELECT @xml1
DECLARE @m varchar(1000)
SET @m =3D'
replace value of (/Tasks/row/@ConversationHandle)[1]
with "' + cast(@ConversationHandle as char(36)) + '"'
SET @xml1.modify(@m) -- this =
errors
SELECT =
@xml1

You can easily do this using SQL variables in the XQuery literal
SET @m ='
replace value of (/Tasks/row/@ConversationHandle)[1]
with sql:variable("@ConversationHandle")'

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities
.

Relevant Pages

  • Re: Inserting Child Elements
    ... column into the xml2 document it gives me > and <... ... This is going to be fairly difficult because the XML DML language doesn't ... Declare @xml1 xml,@xml2 xml ...
    (microsoft.public.sqlserver.xml)
  • Re: Possibility of personalizing OpenOffice documents automatically?
    ... }>> No I want the operators to not be able to modify the original document ... An OOo file is a zip file containing XML files. ... Note that it is content.xml that you want to modify. ... export PROCDIR ...
    (Debian-User)
  • Re: How to split XML file based on data in BizTalk
    ... I cannot modify the source schema. ... I build a custom pipeline component that split the XML document into ...
    (microsoft.public.biztalk.general)
  • Re: How can I replace a value in xml with .modify from the variable?
    ... Well you must always use a string literal in the modify method but you can have access to the values in sql columns or sql variables through the use of sql:columnand sql:variablein your XQuery. ... declare @xml xml, @handle char ... SET @xml1 = @xml ...
    (microsoft.public.sqlserver.xml)
  • Re: Inserting Child Elements
    ... This is going to be fairly difficult because the XML DML language doesn't allow for the insertion of document nodes into elements, ... Declare @xml1 xml,@xml2 xml ... And because you can't do sql:variable, you'll need to bypass XQuery pretty much too. ...
    (microsoft.public.sqlserver.xml)