troubleshooting Question

xml element manipulation in sql server 2005

Avatar of help_me_programming
help_me_programming asked on
Microsoft SQL Server 2005
1 Comment1 Solution316 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros