Link to home
Start Free TrialLog in
Avatar of 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 = 
    <commandId />
    <commandText>wwwwwwwwwww wwwwwwwwwwww wwwww wwwwwww wwwwwwww</commandText>
    <commandId />
    <commandText>abcdefghijkl mlnoioweruweoiriwoe owerweirow</commandText>
    <commandId />
    <commandText>wwwwwwwwwww wwwwwwwwwwww wwwww wwwwwww wwwwwwww</commandText>
    <commandId />
    <commandText>1234567890 1234567890 1234567890 123457890</commandText>
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 )
	 -- 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

Open in new window

Avatar of EugeneZ
Flag of United States of America image

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