replace XML variable in sql

Question

How do I replace the variable value of xml in sql? I need to change the values of ID and Text.

Sample XML

<Values>
   <ValueList>
      <Entry key="Num" type="Values">
         <value ID="1" Text="One" />
      </Entry>
      <Entry key="Name" type="Values">
         <value ID="2" Text="two" />
      </Entry>
   </ValueList>
</Values>

Answer 1

You can modified the ID value like following. Here I am updating ID="1" to "111", similarly you can change the text for a specific text using modify.

DECLARE @XML XML
SET @XML= '<Values> <ValueList> <Entry key="Num" type="Values"> <value ID="1" Text="One" /> </Entry> <Entry key="Name" type="Values"> <value ID="2" Text="two" /> </Entry> </ValueList> </Values>'
SET @XML.modify('replace value of (Values/ValueList/Entry/value[@ID eq ("1")]/@ID)[1] with ("1111")')
select @XML

If you want to use variables, it can be achieved like following.

DECLARE @XML XML
Declare @IDTOReplace VARCHAR(5)='1'
DECLARE @IDWithReplace VARCHAR(5) = '111'
SET @XML= '<Values> <ValueList> <Entry key="Num" type="Values"> <value ID="1" Text="One" /> </Entry> <Entry key="Name" type="Values"> <value ID="2" Text="two" /> </Entry> </ValueList> </Values>'
SET @XML.modify('replace value of (Values/ValueList/Entry/value[@ID eq sql:variable("@IDTOReplace")]/@ID)[1] with sql:variable("@IDWithReplace")')
select @XML

If you want to change the Text based on some Id, it can be achieved like following.

DECLARE @XML XML
Declare @IDTOReplace VARCHAR(5)='1'
DECLARE @TextToReplace VARCHAR(100) = 'NewText'
SET @XML= '<Values> <ValueList> <Entry key="Num" type="Values"> <value ID="1" Text="One" /> </Entry> <Entry key="Name" type="Values"> <value ID="2" Text="two" /> </Entry> </ValueList> </Values>'
SET @XML.modify('replace value of (Values/ValueList/Entry/value[@ID eq sql:variable("@IDTOReplace")]/@Text)[1] with sql:variable("@TextToReplace")')
select @XML