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