1:Checked the data_xml saved in DB as nvarchar(1073741823).So need to convert the format to XML.
Firstly I used cast(data_xml AS XML)
But it reported the err of "XML parsing: line 1, character 56, unable to switch the encoding"
2:It is caused by the xml_data including the encoding="utf-8".So need to replace this.I tried below,it works
CAST(REPLACE(CAST(xml_data AS VARCHAR(MAX)), 'encoding="utf-8"',' ')AS XML)
Actually,if you want to change utf-8 to utf-16,you can REPLACE(CAST(xml_data AS VARCHAR(MAX)), 'encoding="utf-8"','encoding="utf-16"')
3:Then you can use xml_data.modify('replace'),I found below link declare this very clear.
https://www.cnblogs.com/zk-zhou/p/6389185.html
4:Attached with all the script I use
select id,CAST(REPLACE(CAST(data_xml AS VARCHAR(MAX)), 'encoding="utf-8"',' ')AS XML) as data_xml into #t1 from
@table_name(nolock)
where id=123
update #t1
set form_data_xml.modify('
replace value of (/node1/node1_1/node1_1_number/text())[1]
with "123456"
')
update olf1
set olf1.data_xml= cast (t.data_xml as VARCHAR(MAX))
from @table_name olf1
inner join #t1 t on t.id=olf1.id
drop table #t1
Firstly I used cast(data_xml AS XML)
But it reported the err of "XML parsing: line 1, character 56, unable to switch the encoding"
2:It is caused by the xml_data including the encoding="utf-8".So need to replace this.I tried below,it works
CAST(REPLACE(CAST(xml_data AS VARCHAR(MAX)), 'encoding="utf-8"',' ')AS XML)
Actually,if you want to change utf-8 to utf-16,you can REPLACE(CAST(xml_data AS VARCHAR(MAX)), 'encoding="utf-8"','encoding="utf-16"')
3:Then you can use xml_data.modify('replace'),I found below link declare this very clear.
https://www.cnblogs.com/zk-zhou/p/6389185.html
4:Attached with all the script I use
select id,CAST(REPLACE(CAST(data_xml AS VARCHAR(MAX)), 'encoding="utf-8"',' ')AS XML) as data_xml into #t1 from
@table_name(nolock)
where id=123
update #t1
set form_data_xml.modify('
replace value of (/node1/node1_1/node1_1_number/text())[1]
with "123456"
')
update olf1
set olf1.data_xml= cast (t.data_xml as VARCHAR(MAX))
from @table_name olf1
inner join #t1 t on t.id=olf1.id
drop table #t1