xml modify 'replace value of' using sql:variable

Question

I want to modify more than one node in xml data.
Here is a simple scirpt that shows the problem I am having.

declare @XML xml
set @XML = '<Accounts>
                          <subAcc>1</subAcc>
                          <subAcc>2</subAcc>
                          <subAcc>3</subAcc>
                          <subAcc>4</subAcc>
                    </Accounts>'

declare @tmp1 int,@tmp2 int
declare @New_ACC_ID int
set @New_ACC_ID = 6
            select @tmp1 =  count(*) from @XML.nodes('//Accounts')  R(nref)
            set @tmp2=1
            while(@tmp2 <= @tmp1)
            begin
                  set @XML.modify('replace value of (/Accounts/subAcc/text())[sql:variable("@tmp2")] with sql:variable("@New_ACC_ID")')
                  set @tmp2 = @tmp2 +1
            end

This syntex gives me error like  "XQuery [modify()]: The target of 'replace' must be at most one node, found 'text *'"


Now when i wrote "1" instead of  "sql:variable("@tmp2")" everything working fine but this will replace only first element.


Any advise

Thanks in advance

Answer 1

Hello,

Are you expecting this.

declare @XML xml
declare @tmp1 int,@tmp2 int
declare @New_ACC_ID int
set @XML = '<Accounts>
                          <subAcc>1</subAcc>
                          <subAcc>2</subAcc>
                          <subAcc>3</subAcc>
                          <subAcc>4</subAcc>
                    </Accounts>'

set @New_ACC_ID = 6
            select @tmp1 =  count(*) from @XML.nodes('//Accounts/subAcc')  R(nref)
            set @tmp2=1
            while(@tmp2 <= @tmp1)
            begin
				set @XML.modify('replace value of (/Accounts/subAcc[sql:variable("@tmp2")]/text())[1] with sql:variable("@New_ACC_ID")')
                  set @tmp2 = @tmp2 +1
            end
SELECT @XML

Thanks,
Shanthi

Answer 2

You can do it like this using the exist method of the XML data-type:

-- XML Multiple updates using modify with "replace value of" and exist / exists loop
-- with sql:variable
DECLARE @XML XML
SET @XML = '<Accounts>
	<subAcc>1</subAcc>
	<subAcc>2</subAcc>
	<subAcc>3</subAcc>
	<subAcc>4</subAcc>
</Accounts>'

DECLARE @i INT
DECLARE @new_acc_id INT
SET @new_acc_id = 6

SELECT 'before', @xml

SET @i = 1

WHILE @xml.exist( '(Accounts/subAcc[. != sql:variable("@new_acc_id") ])' ) = 1
BEGIN
	SET @xml.modify( 'replace value of (/Accounts/subAcc[. != sql:variable("@new_acc_id") ]/text())[1] with sql:variable("@new_acc_id")' )
	SET @i = @i + 1
	IF @i > 10 BEGIN RAISERROR( 'Too many loops! (%i)', 16, 1, @i ) BREAK END
END

SELECT 'after ', @xml

SELECT @i
GO

Answer 3

set @XML.modify('replace value of (/Accounts/subAcc/@tmp2 )with sql:variable("@New_ACC_ID")')

] with sql:variable("@New_ACC_ID")')

try this it may works

Answer 4

Try:

declare @x xml;

 set @x = N'<Accounts>
    <subAcc>1</subAcc>
    <subAcc>2</subAcc>
    <subAcc>3</subAcc>
    <subAcc>4</subAcc>
</Accounts>';

select @x as x;
 
declare @i int;
declare @New_ACC_ID int;

select @i = @x.value('count(/Accounts/subAcc)', 'int'), @New_ACC_ID = 6;

while @i > 0
begin
    set @x.modify('
	replace value of
		(/Accounts/subAcc[sql:variable("@i")]/text())[1] 
	with 
		sql:variable("@New_ACC_ID")');

    set @i = @i - 1;
end

select @x as x;
go

---

AMB

Some guidelines for posting questions...

Answer 5

Tejal , try this:

declare @XML xml
set @XML = '<Accounts>
                          <subAcc>1</subAcc>
                          <subAcc>2</subAcc>
                          <subAcc>3</subAcc>
                          <subAcc>4</subAcc>
                    </Accounts>'

declare @tmp1 int,@tmp2 int
declare @New_ACC_ID int
set @New_ACC_ID = 6
            select @tmp1 =  count(*) from @XML.nodes('/Accounts/subAcc')  R(nref)
            set @tmp2=1
            while(@tmp2 <= @tmp1)
            begin
                  set @XML.modify('replace value of (/Accounts/subAcc[sql:variable("@tmp2")]/text())[1] with sql:variable("@New_ACC_ID")')
                  set @tmp2 = @tmp2 +1
            end
select @XML 

---

If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Blog : My Blog