Use SQL Server modify('insert') to append data to xml column
<p>Consider the following situation. I have the following table</p>

<pre><code>CREATE TABLE [dbo].[GoldenEgg]
(       
    rowIndex int NOT NULL IDENTITY(1,1),    
    AccountNumber varchar(256) NULL,            
    SubscriptionID int NOT NULL,            
    SubscriptionData_XML xml NULL,
    SubscriptionData_AFTER_XML NULL     

    CONSTRAINT [PK_GoldenEgg] 
        PRIMARY KEY CLUSTERED ([rowIndex] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
</code></pre>

<p>GoldenEgg sample data:</p>

<p><a href="https://i.stack.imgur.com/S5Ssl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/S5Ssl.png" alt="GoldenEgg Table"></a></p>

<p><code>SubscriptionData_XML</code> data for SubscriptionID 6070:</p>

<pre><code>&lt;NVPList xmlns="http://www.whatevernamspace.com/v1" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
  &lt;Item&gt;
    &lt;Name&gt;AccountNumbers&lt;/Name&gt;
    &lt;Value&gt;
      &lt;ValueItem&gt;39448474&lt;/ValueItem&gt;     
    &lt;/Value&gt;
  &lt;/Item&gt;
&lt;/NVPList&gt;
</code></pre>

<p>I want to append all account numbers for each SubscriptionID to the already existing xml <code>&lt;Value&gt;</code> node in the SubscriptionData_XML column and I do not want to add account numbers that already exist in the xml.</p>

<p>So for SubscriptionID 6070 account number 39448474 should only be listed once in the xml like so:</p>

<pre><code>&lt;NVPList xmlns="http://www.whatevernamspace.com/v1" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
  &lt;Item&gt;
    &lt;Name&gt;AccountNumbers&lt;/Name&gt;
    &lt;Value&gt;
      &lt;ValueItem&gt;39448474&lt;/ValueItem&gt;
      &lt;ValueItem&gt;56936495&lt;/ValueItem&gt;
      &lt;ValueItem&gt;70660044&lt;/ValueItem&gt;
      &lt;ValueItem&gt;41447395&lt;/ValueItem&gt;    
    &lt;/Value&gt;
  &lt;/Item&gt;
&lt;/NVPList&gt;
</code></pre>