How Do I Modify First Digit of Attribute in XML with SQL?

Question

This is the section of my xml I am trying to modify:

<ORDER ORDER_NAME="10009999"

ORDER_NAME is an attribute.

This is what I have come up with so far and I think it's close, but slightly off.

update table_name set txn_message.modify('replace value of (/ORDER/@ORDER_NAME)[.=1000][1] with "2000"') , txn_status = 1 

I want to replace 10009999 with 20009999 (really just something else to make it different so data can be reused, adding a additional character is also fine).

Answer 1

One way is

update t 
   set txn_message.modify('replace value of (/ORDER/@ORDER_NAME)[1] with concat("2", sql:column("vr"))')
from table_name t
cross apply (
  select left(t.txn_message.value('(/ORDER/@ORDER_NAME)[1]','varchar(20)'), 1) vl
        , substring (t.txn_message.value('(/ORDER/@ORDER_NAME)[1]','varchar(20)'), 2, 8000) vr
  ) v
where vl = '1';