Link to home
Start Free TrialLog in
Avatar of help_me_programming
help_me_programming

asked on

xml element manipulation in sql server 2005

I declared an xml variable in sql server 2005
and assigned the xml tags as below

now I need to replace the <commandText> node text to first 7 characters.
I am able to do that for one element node... below code shows that

How to do that dynamically in the loop because, I am not sure of many commandText elements will be there.  There can be 'n' <commandText> nodes. Below code shows the looping through the nodes
declare @x xml
set @x = 
'<commandOptions>
  <commandOption>
    <commandId />
    <commandText>wwwwwwwwwww wwwwwwwwwwww wwwww wwwwwww wwwwwwww</commandText>
  </commandOption>
  <commandOption>
    <commandId />
    <commandText>abcdefghijkl mlnoioweruweoiriwoe owerweirow</commandText>
  </commandOption>
  <commandOption>
    <commandId />
    <commandText>wwwwwwwwwww wwwwwwwwwwww wwwww wwwwwww wwwwwwww</commandText>
  </commandOption>
  <commandOption>
    <commandId />
    <commandText>1234567890 1234567890 1234567890 123457890</commandText>
  </commandOption>
</commandOptions>
'
set @x.modify('
    replace value of (/commandOptions/commandOption/commandText/text())[1]
    with  substring((/commandOptions/commandOption/commandText)[1], 1, 7)' )
 
select @x
 
declare @iCount int
set @iCount = @x.value('count(//commandText)','int')
 
declare @i int
set @i = 1
 
while (@i <= @icount )
	begin
	 -- can some one fill up the code to do the replace the text to first seven characters dynamically for all commandText nodes
 
	 print 'working with xml manipulation'
	 set @i = @i + 1
	end

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial