Use of Xml.Modify to Insert Parameters into Specific Element of an Xml Column

  • Home
  • Languages
  • SQL
  • Use of Xml.Modify to Insert Parameters into Specific Element of an Xml Column

Use of xml.modify to insert parameters into specific element of an xml column

declare @XML xml = '
<Profile>
  <ID>20</ID>
  <User>
    <ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>
    <Name>somename</Name>
    <Activities>
      <Activity>
         <Name>activity1</Name>
      </Activity>
    </Activities>
  </User>
</Profile>'

declare @user_id nvarchar(50) = '20'
declare @activity_name nvarchar(50) = 'activity1'
declare @display_name nvarchar(50) = 'displayname1'

set @xml.modify('insert <DisplayName>{sql:variable("@display_name")}</DisplayName>
                 into (/Profile[ID = sql:variable("@user_id")]
                       /User/Activities/
                       Activity[Name = sql:variable("@activity_name")])[1]')

Result:

<Profile>
  <ID>20</ID>
  <User>
    <ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>
    <Name>somename</Name>
    <Activities>
      <Activity>
        <Name>activity1</Name>
        <DisplayName>displayname1</DisplayName>
      </Activity>
    </Activities>
  </User>
</Profile>

SQL: Use modify function to iterate on xml nodes or How to insert or modify nodes recursively

You can use modify with insert, but the main drawback seems to be that SQL server only allows insert into a single node, I couldn't combine it with a FLOWR expression, so I had to use an SQL WHILE loop to perform various modify calls:

DECLARE @XML XML =
N'<Steplist>
  <Step>
    <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>     
  </Step>
  <Step>
    <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>';

BEGIN

DECLARE @i int;

SELECT @i = @XML.value('count(Steplist/Step)', 'int');

WHILE @i > 0
  BEGIN

    SET @XML.modify('insert <TextReadingId>{sql:variable("@i")}</TextReadingId> as last into (/Steplist/Step[sql:variable("@i")])[1]');

    SET @i = @i - 1;
  END
END
SELECT @XML;

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

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:
AccountNumberXml

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:
Sample Image

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:
CurrentValueItems

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

Insert XML element with attribute into SQL XML column

First of all - for your next question: Please do not post pictures! Try to set up a test-scenario. Please read How to ask a good SQL question and How to create a MCVE.

About your question

Your code - on the first sight - should work. But you obviously modified it to fit to this forum.

DECLARE @myTable TABLE(ID VARCHAR(100),Column1 XML)
INSERT INTO @myTable VALUES
 ('111'
  ,'<SettingValues>
     <Setting Name="A-name" Value="A-value"/>
   </SettingValues>')
, ('222'
  ,'<SettingValues>
     <Setting Name="A-name" Value="A-value"/>
   </SettingValues>');

UPDATE @MyTable 
SET Column1.modify('insert <Setting Name="H" Value="0"/> as last into (/SettingValues)[1]')
WHERE ID = '222';

SELECT * 
FROM @myTable

This works as expected.

ID  Column1
111 <SettingValues><Setting Name="A-name" Value="A-value" /></SettingValues>
222 <SettingValues><Setting Name="A-name" Value="A-value" /><Setting Name="H" Value="0" /></SettingValues>

After execution you see "1 row affected".

Some ideas:

  • The filter is not fullfilled
  • The given XML declares namespaces but never uses them... This is a bit odd. Have you reduced the namespaces and your original XML includes a default namespace (or the elements are prefixed somehow)?
  • You are checking against the wrong target (other server, other schema, other table...

But: The code above should work...

sql xquery to add xml block into specific position

You can use insert ... after ... construct to insert new element after certain existing element. Example below inserts new element after the existing <Animal name="Cat"> element :

declare @data XML = '<Animals>
  <Animal name="Dog">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
  <Animal name="Cat">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
  <Animal name="Bird">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
  <Animal name="Sheep">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
</Animals>'

set @data.modify('
insert 
    <Animal name="Goat">
        <Enclosure id="Default">
          <Value>xyz</Value>
        </Enclosure>
    </Animal>
after (/Animals/Animal[@name="Cat"])[1]
')

Inserting node in XML using SQL

No need for any complicated hassel. Just insert the node you want as you want it:

UPDATE #temp SET xml_data.modify('insert <type>Q</type> into (/Main)[1]');

Using as first, as last or before / after allows you to specify the node's position. The following will place the new node directly after <name>:

UPDATE #temp SET xml_data.modify('insert <type>Q</type> after (/Main/name)[1]');

UPDATE Your question about an update-statement

Your statement has several flaws:

UPDATE #temp
     SET xml_data = 
        case
            when @type = 'G' 
            then xml_data.modify('insert <type>G</type> into (/Main)[1]');
            when @type = 'Q' 
            then xml_data.modify('insert <type>Q</type> into (/Main)[1]'); 
         end

You cannot use the syntax SET xmlColumn = xmlColumn.modify(). You have to use SET xmlColumn.modify(), Furthermore the semicolons are breaking this anyway.

To be honest, I think this is to complicated, try this: 

DECLARE @type VARCHAR(1)='Q'
UPDATE #temp SET xml_data.modify('insert <type>{sql:variable("@type")}</type> into (/Main)[1]');

This will create a new node <type>content</type>, with a content taken out ot the variable @type.

Get and update XML value with a certain attribute

Unfortunately, MS SQL Server XQuery implementation is not fully conformant with the XQuery standards. That's why XQuery .modify() method makes one update at a time.

So we can update XML in a loop by using the XQuery .exist() method until there is nothing to update.

Please up-vote my suggestion: https://feedback.azure.com/d365community/idea/153a9604-7025-ec11-b6e6-000d3a4f0da0

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<array>
    <foo>
        <property name="Name">Foo 1</property>
        <property name="Gender">M</property>
        <property name="DOB">2020-01-01</property>
    </foo>
    <foo>
        <property name="Name">Foo 2</property>
        <property name="Gender">M</property>
        <property name="DOB">2020-01-02</property>
    </foo>
</array>'),
(N'<array>
    <foo>
        <property name="Name">Foo 3</property>
        <property name="Gender">F</property>
        <property name="DOB">2020-01-03</property>
    </foo>
</array>');
-- DDL and sample data population, end

DECLARE @from VARCHAR(30) = '1'
   , @to VARCHAR(30) = '1'
   , @UPDATE_STATUS BIT = 1;

-- before
SELECT * FROM @tbl
WHERE xmldata.exist('/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]') = 1;

WHILE @UPDATE_STATUS > 0
BEGIN
   UPDATE t
   SET xmldata.modify('replace value of (/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]/text())[1]
      with (sql:variable("@to"))')
   FROM @tbl AS t
   WHERE xmldata.exist('/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]') = 1;
    
   SET @UPDATE_STATUS = @@ROWCOUNT;
   PRINT @UPDATE_STATUS;
END;

-- after
SELECT * FROM @tbl;

How to insert an element into xml column not knowing if the tree will already exist?

You can include the nesting structure in your insert statement and do it with just one update like this:

UPDATE #xmlTable
SET xmlCol.modify('
insert if (count(/root)=0) then <root><colors><color>{sql:column("color")}</color></colors></root> 
else (if (count(/root/colors)=0) then <colors><color>{sql:column("color")}</color></colors> 
else <color>{sql:column("color")}</color>) as first into 
(if (count(/root)=0) then (/) else (if (count(/root/colors)=0) then (/root) else (/root/colors)))[1]')
FROM #xmlTable
INNER JOIN #tableColors
    ON #xmlTable.id = #tableColors.id
WHERE xmlCol.exist('/root/colors/color[(text()[1])=sql:column("color")]') = 0

Related Topics

Get Month and Year from a Datetime in SQL Server 2005

How to Preview a Destructive SQL Query

How to Drop Table Variables in SQL-Server? Should I Even Do This

MySQL Auto-Store Datetime for Each Row

How to Select SQL Results Based on Multiple Tables

How to Load SQL Fixture in Django for User Model

SQL Get All Records Older Than 30 Days

SQL Server Int or Bigint Database Table Ids

Padding Zeros to the Left in Postgresql

Singular or Plural Database Table Names

Insert Multiple Rows into Single Column

Prevent Recursive Cte Visiting Nodes Multiple Times

Include in Select a Column That Isn't Actually in the Database

Export Database Schema into SQL File

How to Compare Values Which May Both Be Null in T-Sql

Database-Wide Unique-Yet-Simple Identifiers in SQL Server

Extract Day of Week from Date Field in Postgresql Assuming Weeks Start on Monday

Limiting an SQL Join


Leave a reply



Submit