How to replace value in XMLColumn in SQL tabel

Question

I have this XML where i want to change the value of 97 in the node 'txtqtyremaining' into a new value. How should i do that?

I don't know how to select to correct node and how to change that value.`

    <genxml>
  <files />
  <hidden />
  <textbox>
    <txtproductref>SKU</txtproductref>
  </textbox>
  <dropdownlist />
  <checkboxlist />
  <radiobuttonlist />
  <models>
    <genxml>
      <files />
      <textbox>
        <availabledate datatype="date" />
        <txtbarcode>BAR1234</txtbarcode>
        <txtqtyremaining datatype="double">97</txtqtyremaining>
      </textbox>

"I don't know how to select to correct node " Does this mean that path is unknown or variable? — John Cappelletti, Oct 19 '16 at 16:42
I'm using SQL server. And i want to know the correct UPDATE statement. — user7043412, Oct 19 '16 at 16:55
Possible duplicate of [How does one parse XML files?](http://stackoverflow.com/questions/55828/how-does-one-parse-xml-files) — n.y, Oct 19 '16 at 19:08

score 1 · Accepted Answer · answered Oct 19 '16 at 19:20

SET @XML.modify('replace value of (/genxml/models/genxml/textbox/txtqtyremaining[1]/text())[1] with "999.99"') 
Select @XML

Or you can use a simple Replace()

Set @XML = Replace(cast(@XML as varchar(max)),'>97</txtqtyremaining>','>999.999</txtqtyremaining>')
Select @XML

Both would Return

<genxml>
  <files />
  <hidden />
  <textbox>
    <txtproductref>SKU</txtproductref>
  </textbox>
  <dropdownlist />
  <checkboxlist />
  <radiobuttonlist />
  <models>
    <genxml>
      <files />
      <textbox>
        <availabledate datatype="date" />
        <txtbarcode>BAR1234</txtbarcode>
        <txtqtyremaining datatype="double">999.999</txtqtyremaining>
      </textbox>
    </genxml>
  </models>
</genxml>

How to replace value in XMLColumn in SQL tabel

1 Answers1