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 = 
    <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


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