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

Open in new window

ASKER CERTIFIED SOLUTION
EugeneZ
SQL SERVER EXPERT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
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