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

Question

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>

score 2 · Answer 1 · answered Nov 10 '16 at 09:55

If there are not other nodes within your XML you might choose the FLWOR-query.

Some hints:

first I create a mock-up table and fill it with data
I use and updateable CTE to collect the data
I use a FOR XML-sub-select without a namespace to build the <Value> node wihtout bothering about already existing IDs in your actual XML
I use a FLWOR-query() to build up the full XML out of the just created Value-node
As this CTE is updateable, I can use it directly for the UPDATE
The final SELECT * FROM @tbl shows to you, that all AFTER_XML are filled

Try this:

DECLARE @tbl TABLE(rowIndex INT IDENTITY,AccountNumber INT,SubscriptionID INT, SubscriptionData_XML XML,SubscriptionData_AFTER_XML XML);
INSERT INTO @tbl VALUES
 (1111,6070,N'<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>',NULL)
,(2222,6070,NULL,NULL)
,(3333,6070,NULL,NULL)
,(4444,6070,NULL,NULL)
,(5555,6071,N'<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>',NULL)
,(6666,6071,NULL,NULL)
,(7777,6071,NULL,NULL)
,(8888,6071,NULL,NULL);

--Here starts the updateable CTE

WITH UpdateableCTE AS
(
    SELECT t1.rowIndex
          ,t1.SubscriptionData_AFTER_XML
          ,(
            SELECT t2.AccountNumber AS ValueItem
            FROM @tbl AS t2
            WHERE t2.SubscriptionID=t1.SubscriptionID
            FOR XML PATH(''),ROOT('Value'),TYPE
           ).query
                (N'declare default element namespace "http://www.whatevernamspace.com/v1";
                   let $nd:=/*:Value
                   return
                   <NVPList>
                       <Item>
                          <Name>{sql:column("XmlName")}</Name>
                          <Value>
                           {
                           for $vi in $nd/*:ValueItem
                           return <ValueItem>{$vi/text()}</ValueItem>
                           }
                          </Value>
                       </Item>
                   </NVPList>
                  '
                ) AS NewXML

    FROM @tbl AS t1
    CROSS APPLY( SELECT t1.SubscriptionData_XML.value('(//*:Name)[1]','nvarchar(max)') AS XmlName) AS x
    WHERE SubscriptionData_XML IS NOT NULL
)

--The UPDATE statement

UPDATE UpdateableCTE SET SubscriptionData_AFTER_XML=NewXML
FROM UpdateableCTE;

--The SELECT to check the success

SELECT * FROM @tbl

when I wrote this post it was to accept my own answer, as it was a solution that I've already implemented. You have to wait 48hrs to do that on stackoverflow. Your solution is great, also with your solution you are basically rewriting the xml with just the account numbers (1111,2222,3333,4444) which is replacing the already existing value of 39448474. I wanted to leave all the original ValueItem's — iambdot, Nov 14 '16 at 14:57
@iambdot That's perfectly OK! It's on you to accept the answer you like most... Happy coding! — Shnugo, Nov 14 '16 at 14:59

iambdot · Accepted Answer · 2016-11-10T04:57:56.230

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

If you also want some way to suppress namespace attributes in nested `for xml` statements (without having to resort to string manipulation), please upvote the following Microsoft Connect item: [suppress namespace attributes in nested SELECT FOR XML statements](https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements). — TT., Nov 10 '16 at 06:44

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

2 Answers2

Problem 1

Problem 2