SQL Server xml variable is not by reference

user_6587991 0
Viewed 42

the following query should append node to an exist xml. based on the output xml variable is actually new node and not reference of the exists one. can I use reference variable please advise:

declare @a_bo_key_xml xml='<r><items><item><key>k1</key><value>v1</value></item></items></r>'
Declare @rowsBOK xml=@a_bo_key_xml.query('/r/items/item')
select  @rowsBOK.value('(*/key)[1]','varchar(100)'), @rowsBOK.value('(*/value)[1]','varchar(100)')
set @rowsBOK.modify('insert <added>aaa</added> as first into (*)[1]') 
select cast(@a_bo_key_xml as varchar(max))
select cast(@rowsBOK  as varchar(max))

output:
<r><items><item><key>k1</key><value>v1</value></item></items></r>
<item><added>aaa</added><key>k1</key><value>v1</value></item>

expected:
<r><items><item><added>aaa</added><key>k1</key><value>v1</value></item></items></r>
<item><added>aaa</added><key>k1</key><value>v1</value></item>
xml sql-server reference sql variables
1 Answers

XML variables take a new identity when re-assigned, there is no connection between one and the other.

Just modify the @a_bo_key_xml variable directly:

declare @a_bo_key_xml xml='<r><items><item><key>k1</key><value>v1</value></item></items></r>';
set @a_bo_key_xml.modify('insert <added>aaa</added> as first into (/r/items/item)[1]');
select @a_bo_key_xml;

db<>fiddle

user_148689970
Related