CODEWITHSUNDEEP
sqlxmlsql-server-2008
Mikhail Sokolov
Mikhail Sokolov

Reputation: 556

sql modify XML node

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?

Upvotes: 1

Views: 92

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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"

Upvotes: 1

Related Questions