0

I want to make modifications to an XML document using SQL Server's XML.modify. My problem is my XML document uses some escaped XML so "<" are appearing as "<" and ">" is appearing as ">". I want to know if it would be possible to set the value of an element that is surrounded by escaped XML. An example of what I'm dealing with is below:

Declare @myDoc as xml;
Set @myDoc = '<Root>       
                <ProductDescription ProductID="1" ProductName="Road Bike">       
                    <Features>
                        <BikeLight>False</BikeLight>
                        &lt;BikeHorn&gt;True&lt;/BikeHorn&gt;    
                    </Features>       
                </ProductDescription>       
            </Root>'  ;

I know I can edit the value of the BikeLight element by using

set @myDoc.modify('replace value of (/Root/ProductDescription/Features/BikeLight/text())[1] with "True"')

but trying to do the same with BikeHorn only returns the XML document, unmodified. Is it possible to modify the value of elements surrounded by escaped XML? Any help would be appreciated, thanks. Also, just to note that in my actual code all elements under Features would be surrounded by escaped XML.

Habeeb Adisa
  • 3
  • 1

1 Answers1

0

The problem is you don't have a node called <BikeHorn>, you have a complex node <Features> which contains some text of its own in addition to a <BikeLight> child node. So you need to modify <Features> to change the value of BikeHorn:

set @myDoc.modify('
replace value of (/Root/ProductDescription/Features/text())[1]
with "&lt;BikeHorn&gt;False&lt;/BikeHorn&gt;"')
Code Different
  • 90,614
  • 16
  • 144
  • 163
  • Great that worked perfectly. Saved me alot of headache. – Habeeb Adisa Feb 10 '15 at 19:25
  • Hi, what if contain more text that just <BikeHorn>False</BikeHorn>. What if it was formatted like: <BikeHorn>True</BikeHorn> <ExtraSeat>True</ExtraSeat> Would I need to include both ExtraSeat as well as BikeHorn in the "with" portion of the replace statement if I just wanted to modify the value of BikeHorn? – Habeeb Adisa Feb 10 '15 at 20:02
  • You are manipulating text, not XML. Is there any reason for the XML to be as mangled as it is? – Code Different Feb 10 '15 at 20:04
  • This is xml that wasn't generated by me. I'm not sure why this is the way it is but I'm trying to work with it. Since it's text I think I can just use some kind of replace to modify that text that way. Thanks again for the help. – Habeeb Adisa Feb 10 '15 at 20:13
  • You can resolve this issue once and for all by replacing all the entities with real brackets. Then you'll be working with properly formed XML. – Code Different Feb 10 '15 at 20:15