user7043412 user7043412 - 1 month ago 12
SQL Question

How to replace value in XMLColumn in SQL tabel

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>

Answer
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>
Comments