sql modify XML node

Question

I'm trying to modify xml attribute in my table:

XML:

 <root>
      <object name="111">
        <fields>
          <field name="1">False</ofield>
          <field name="VIN">123</field>
         </fields>
     </object>
    </root>


UPDATE wftable
SET XML.modify('replace value of 
               (root/object[@name="111"]/fields/field/@name[.="VIN"])[1] 
               with "testNumber"')
WHERE id = 20889436

But I get as a result

<field name="testNumber">123</field>

Actually I just want to update xml node like this:

<field name="VIN">testNumber</field>

How can I modify my UPDATE query?

Mikael Eriksson · Accepted Answer · 2014-12-26 09:04:59Z

1

You need to specify the text() node of field as the node you want to update.

replace value of 
(root/object[@name="111"]/fields/field[@name="VIN"]/text())[1] 
with "testNumber"

answered Dec 26, 2014 at 9:04

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql modify XML node

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related