Use SQL Server modify('insert') to append data to xml column

Consider the following situation. I have the following table

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]

GoldenEgg sample data:

SubscriptionData_XML data for SubscriptionID 6070:

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

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

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

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

I was able to accomplish this task with a sql UPDATE statement using the xml modify() method and without using any loops. Here is a breakdown of the solution:

1) I had to get all the AccountNumbers for the SubscriptionID and format them in into xml <ValueItem> nodes.

SQL QUERY 1:

SELECT 
ge.SubscriptionID,
CAST((SELECT DISTINCT ValueItem = ISNULL(ge2.AccountNumber,'')
        FROM dbo.GoldenEgg ge2
        WHERE ge2.SubscriptionID = ge.SubscriptionID                        
        FOR XML PATH('')) AS xml) AS AccountNumberXml
FROM dbo.GoldenEgg ge
WHERE ge.SubscriptionData_XML IS NOT NULL

SQL QUERY 1 RESULT:

SQL QUERY 1 XML RESULT (SubscriptionID 6070):

<ValueItem>39448474</ValueItem>
<ValueItem>41447395</ValueItem>
<ValueItem>56936495</ValueItem>
<ValueItem>70660044</ValueItem>

2) Now that I have the AccountNumbers in a single value, I can now use the xml modify() method and insert the AccountNumberXml value into the last position of the <Value> xml node. I will do this using an UPDATE statement with INNER JOIN. Also note that I initally set SubscriptionData_AFTER_XML equal to SubscriptionData_XML before doing anything.

SQL QUERY 2:

UPDATE ge
    SET SubscriptionData_AFTER_XML.modify
    ('declare default element namespace "http://www.whatevernamspace.com/v1";
      insert sql:column("t1.AccountNumberXml") as last into (/NVPList/Item/Value)[1]')          
    FROM dbo.GoldenEgg ge
    INNER JOIN (SELECT 
                ge2.SubscriptionID,
                CAST((SELECT DISTINCT ValueItem = ISNULL(ge1.AccountNumber,'')
                        FROM dbo.GoldenEgg ge1                                              
                        WHERE ge1.SubscriptionID = ge2.SubscriptionID                       
                        FOR XML PATH('')) AS xml) as AccountNumberXml
                FROM dbo.GoldenEgg ge2
                WHERE ge2.SubscriptionData_AFTER_XML IS NOT NULL) t1 ON t1.SubscriptionID = ge.SubscriptionID
    WHERE ge.SubscriptionData_AFTER_XML IS NOT NULL

SQL QUERY 2 RESULT:

SQL QUERY 2 XML RESULT (SubscriptionID 6070 SubscriptionData_AFTER_XML column):

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

As you may see there are now two problems with the final xml result in the SubscriptionData_AFTER_XML column.

Problem 1

For subscriptionID 6070 AccountNumber 39448474 is being repeated in the <ValueItem> node list, which I do not want. To fix this I have to query the current AccountNumber values in the xml and exclude those AccountNumbers from the previous INNER JOIN

SQL QUERY 3:
This query will give me a result set with all the current AccountNumbers in the SubscriptionData_XML column, which I can then use to exclude these AccountNumbers from the SQL QUERY 1 result set

SELECT SubscriptionID, t.c.value('.', 'varchar(MAX)') as CurrentValueItems
FROM dbo.GoldenEgg 
CROSS APPLY SubscriptionData_XML.nodes('declare default element namespace "http://www.whatevernamspace.com/v1";
                                    /NVPList/Item/Value/ValueItem') as t(c)
WHERE SubscriptionData_XML IS NOT NULL

SQL QUERY 3 RESULT:

Now putting it all together to get the correct final result

SQL QUERY 4:

UPDATE ge
SET SubscriptionData_AFTER_XML.modify
('declare default element namespace "http://www.whatevernamspace.com/v1";
  insert sql:column("t1.AccountNumberXml") as last into (/NVPList/Item/Value)[1]')          
FROM dbo.GoldenEgg ge
INNER JOIN (SELECT 
            ge2.SubscriptionID,
            CAST((SELECT DISTINCT ValueItem = ISNULL(ge1.AccountNumber,'')
                    FROM dbo.GoldenEgg ge1
                    --make sure we are not inserting AccountNumbers that already exists in the subscription data
                    WHERE ge1.AccountNumber NOT IN (SELECT t.c.value('.', 'varchar(MAX)') as CurrentValueItems
                                                    FROM dbo.GoldenEgg 
                                                    CROSS APPLY SubscriptionData_XML.nodes('declare default element namespace "http://www.whatevernamspace.com/v1";
                                                                                     /NVPList/Item/Value/ValueItem') as t(c)
                                                    WHERE SubscriptionData_XML IS NOT NULL
                                                    AND SubscriptionID = ge2.SubscriptionID) 
                    AND ge1.SubscriptionID = ge2.SubscriptionID                     
                    FOR XML PATH('')) AS xml) as AccountNumberXml
            FROM dbo.GoldenEgg ge2
            WHERE ge2.SubscriptionData_AFTER_XML IS NOT NULL) t1 ON t1.SubscriptionID = ge.SubscriptionID
WHERE ge.SubscriptionData_AFTER_XML IS NOT NULL

SQL QUERY 4 XML RESULT (SubscriptionID 6070 SubscriptionData_AFTER_XML column):

As you can see AccountNumber 39448474 is now only listed once in the xml

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

Problem 2

When the with AccountNumber node list is inserted, it is being inserted with an empty xmlns="" namespace. This is query I used to remove the empty xmlns="" namespace.

SQL QUERY 5:

UPDATE dbo.GoldenEgg
SET SubscriptionData_AFTER_XML = CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), SubscriptionData_AFTER_XML), N'xmlns=""',''))
WHERE SubscriptionData_AFTER_XML IS NOT NULL

SQL QUERY 5 XML RESULT (SubscriptionID 6070):

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

I hope this helps anyone who may need to do something similar