Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Examples of using XQuery to update XML Data in SQL Server

By:   |   Read Comments (19)   |   Related Tips: More > XML


What is the most efficient way to modify XML values? To allow for precise modification of XML nodes and values, the XQuery XML query language employs an extension known as the XML Data Modification Language (DML). The XML DML uses the XML modify() method, which makes use of three operational keyword sets: insert, replace value of, and delete. Let's take a closer look.


Inserting Nodes

The insert keyword uses the following structure:

    insert Expression1 ( {as first | as last} into | after | before Expression2 )

The insert operation can be used to insert nodes into, after, or before existing nodes. In order to demonstrate some examples, let's create a table with a record containing sample untyped XML:

        <Employee ID="1" tier="4">
        <Employee ID="2" tier="1">
        <Employee ID="3" tier="4">

If we would like to create a new department node named ‘Accounting', we can do so using modify() and insert:

SET Salaries.modify('insert <Accounting /> into (/Salaries)[1]')

The Accounting node was added inside the Salaries node by simply using the insert...into format.

The Accounting node was added inside the Salaries node by simply using the insert...into format.

Singleton Designation

You may notice that we designated a singleton (single node value) representation of ‘[1]' for Expression 2 (the Salaries destination node). This is because the insert keyword requires that Expression 2 be a single node. Even though there is only one Salaries node, the singleton is still required. Attempting to run the query without the singleton will result in an error:

SET Salaries.modify('insert <Accounting /> into (/Salaries)')

Singleton Designation

Insert Node as First

We see that the node was placed after the only other existing node residing on the same level (the Marketing node) by default. Instead, we want the Accounting node to be the first node under the Salaries node. To make this change, we will use the format insert...as first:

SET Salaries.modify('insert <Accounting /> as first into (/Salaries)[1]')

Insert Node as First

We now have one empty Accounting node at the top, and one at the bottom. We'll remove the bottom one later.

We are told that we have a new employee joining the Accounting department. Let's populate the Accounting node with a new Employee node, which should include node attributes (ID, tier):

SET Salaries.modify('insert <Employee ID="4" tier="4" /> into (/Salaries/Accounting)[1]')

Let's populate the Accounting node with a new Employee node, which should include node attributes (ID, tier):

Going back for a moment to the subject of singletons; if we had used '[2]' as the singleton designation, the new Employee node would have been placed in the bottom Accounting node.

Insert by Identifying a Specific Node

What if we needed to insert a new node into a specific Employee node in the Marketing department? We can identify the correct employee by their ID attribute, using the ‘@ID' argument:

SET Salaries.modify('insert <Projects /> into (/Salaries/Marketing/Employee[@ID=("2")])[1]')

Insert by Identifying a Specific Node

The Projects node has been inserted into the Employee node having ID #2.

Insert Nodes with Values

We can also insert any collection of nested nodes and values. If we want to designate a new project for employee ID #2, we can do the following:

SET Salaries.modify('insert <Project ID="1"><Description>Organize new 
    strategies</Description></Project> into 

Insert Nodes with Values

Insert Nodes with Values from Other Nodes

The newest employee now needs salary data. Employee #4 is at tier 4, like employee #1, and should be at the same salary range as employee #1. Let's insert a Salary node for employee #4, and populate it with the salary amount from employee #1's Salary node:

SET Salaries.modify('insert 
into (/Salaries/Accounting/Employee[@ID=("4")])[1]')

Insert Nodes with Values from Other Nodes

Notice that we've pulled the salary amount from employee #1 by using curly braces ({}) around the path, and then wrapped it all with explicitly depicted Salary node tags.

Deleting Nodes

The delete keyword is much simpler than insert. Its syntax structure looks like:

    delete Expression

Let's remove the redundant bottommost Accounting node from the Salaries node:

SET Salaries.modify('delete (/Salaries/Accounting)[2]')

We've deleted the empty node by indicating its singleton value (2).

Deleting Values

Node values can be deleted by employing the text() function to specify that a value, not a node, is to be removed.

The project description for project #1 has become outdated. We need to remove the description for the time being. Only employee #2 is working on this project, so we can run the following to remove the value:

SET Salaries.modify('delete 

Deleting Values

The Description node's value has been successfully removed.

Replacing Values

We can use replace value of to change node values. The syntax format for replace value of is as follows:

    replace value of Expression1 with Expression2

We realize that the salary amount for employee #2 is incorrect. We need to change it from 52000 to 60000. To update it to the new value, we'll run the following:

SET Salaries.modify('replace value of 
(/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")')

Replacing Values

The salary amount has been updated.

Replace Values Using XQuery Arithmetic

We now find out that ALL employees in the Marketing department are to receive a 10% salary increase. We can use a script that iterates Employee nodes appropriately:

WHILE @i <= 3
 SET Salaries.modify('replace value of 
 with (/Salaries/Marketing/Employee[@ID=(sql:variable("@i"))]/Salary)[1] * 1.01')
 SET @i+=1

Replace Values Using XQuery Arithmetic

We've used the XQuery multiplication operator to increase the existing value of every Salary node in Marketing by 10%. We incorporated the SQL variable '@i' (inside of a sql:variable() extension function), which was used to iterate the Employee nodes. Notice that the salary amount for employee # 4 was not updated, since that employee is in the Accounting department.

Modify() Limitations

A couple of operations involving the modify() method will not work as may be expected:

Issue # 1

The modify() method cannot be used to select the results of the XQuery modify statement. The following will fail:

SELECT Salaries.modify('replace value of 
with ("60000")')

Modify() Limitations

This is because modify() operates directly on the XML value, whether it is a column or variable, and cannot be used in an ad-hoc operation that does not update the underlying XML value. An alternative to the above operation would be to select the XML value into a new XML variable, make the change, and then select the variable value:

SELECT @x = Salaries FROM HR_XML
SET @x.modify('replace value of (/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")')

The above method can be used to check or test the modification script before actually applying it to the original XML value.

Issue # 2

Modify() also cannot be used more than once within the same UPDATE statement, on the same XML value. In other words, only one instance of modify() may be used at the same time on the XML value. For example, an operation that tries to update the salary amount of two different employees in the following manner will fail:

SET Salaries.modify('replace value of 
(/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")'),
Salaries.modify('replace value of 
(/Salaries/Marketing/Employee[@ID=("1")]/Salary/text())[1] with ("60000")')

 Modify() also cannot be used more than once within the same UPDATE statement

Instead, multiple updates to the same XML value must be accomplished using entirely separate UPDATE statements.

Replace Value of one Node with Value of Another

Employee #1 has reached tier 1 status. We need to update the salary amount to match that of employee #2 (60600):

SET Salaries.modify('replace value of 
with (/Salaries/Marketing/Employee[@ID=("2")]/Salary)')

Replace Value of a Node Attribute

We also need to change the tier number for employee #1. To specify a node attribute in the XQuery path, use the ‘@' symbol:

SET Salaries.modify('replace value of 
(/Salaries/Marketing/Employee[@ID=("1")]/@tier)[1] with "1"')

We've successfully changed the salary amount and tier number for employee #1. The results after running both statements are as follows:

Replace Value of a Node Attribute

Notice that we had to perform two separate modify() operations in order to update the salary and tier data.


We've looked at examples of the XML DML extension, using the modify() XQuery method. We introduced all three operational keyword sets: insert, replace value of, and delete; and used them to perform some of the more common XML modification operations. We also pointed out some limitations and appropriate workarounds.

Next Steps
  • In all of our examples here, we've used untyped XML. If you are interested in furthering your XML DML skills, I suggest becoming familiar with performing the operations we have covered in this article on typed XML. An excellent article by Robert Sheldon will get you started.
  • Read these other tips related to XML

Last Update:

About the author
MSSQLTips author Seth Delconte Seth Delconte is a Microsoft Certified Trainer and Database Administrator, and works primarily on SQL Server development.

View all my tips

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Get free SQL tips:

*Enter Code refresh code     

Friday, May 19, 2017 - 12:20:37 PM - Scott Back To Top

I may be late to the party but I couldn't help replying to Yadnesh's question from Jan 2014 about bulk updates.

XQUERY will let you do one replacement.  If you want to do a bulk update, you either do multiple XQUERY operations in a loop or you extract the data from XML, do the required update on every row, and rebuild a new XML value with all the updates.  If you've got to do 15,000 updates to one piece of XML, XQUERY will be very slow.

SET @OrigXml = '
  <DealNumber> 1001</DealNumber>
      <DealLineId>1 </DealLineId>
      <DealLineNumber>ABC1 </DealLineNumber>
      <Department DepartmentName="DepartmentName3"/>
      <DealLineId>2 </DealLineId>
      <DealLineNumber> XYZ2</DealLineNumber>
      <Department DepartmentName="DepartmentName2"/>

-- Verify all the XPATH expressions required to extract all date from XML
SELECT  DealNumber = DealLineNode.value('../../DealNumber[1]', 'int'),
             DealLineId = DealLineNode.value('DealLineId[1]', 'int'),
             DealLineNumber = DealLineNode.value('DealLineNumber[1]', 'varchar(100)'),
             DealDepartment = DealLineNode.value('Department[1]/@DepartmentName', 'varchar(100)')
FROM @OrigXml.nodes('/Deal/DealLines/DealLine') x(DealLineNode);

DealNumber  DealLineId  DealLineNumber  DealDepartment
1001        1           ABC1            DepartmentName3
1001        2            XYZ2           DepartmentName2

-- Reproduce the entire XML structure with a SELECT query
-- Make updates where necessary, save in a XML variable or column
SET @NewXML = (
    SELECT  DealNumber = Deal.value('DealNumber[1]', 'int') + 1000,
            DealLines = (
                SELECT  DealLineId = DealLine.value('DealLineId[1]', 'int') + 50,
                        DealLineNumber = DealLine.value('DealLineNumber[1]', 'varchar(100)') + '-2',
                        -- WHERE can't be used to filter rows, all data must be passed to the new XML structure
                        -- But CASE can be used to only edit selected values
                        [Department/@DepartmentName] = CASE WHEN DealDepartment LIKE '%2%' 
                                                            THEN REPLACE(DealDepartment, '2', '42') 
                                                            ELSE DealDepartment END
                FROM Deal.nodes('DealLines/DealLine') x2(DealLine)
                CROSS APPLY ( SELECT DealDepartment = DealLine.value('Department[1]/@DepartmentName', 'varchar(100)') ) dd
                FOR XML PATH('DealLine'), TYPE)
    FROM @OrigXml.nodes('/Deal') x1(Deal)
    FOR XML PATH('Deal'), TYPE);

SELECT @OrigXml, @NewXML;
/* Original                                                                         Updated
<Deal>                                                                            <Deal>
  <DealNumber> 1001</DealNumber>                                  <DealNumber>2001</DealNumber>
  <DealLines>                                                                     <DealLines>
    <DealLine>                                                                       <DealLine>
      <DealLineId>1 </DealLineId>                                             <DealLineId>51</DealLineId>
      <DealLineNumber>ABC1 </DealLineNumber>                        <DealLineNumber>ABC1 -2</DealLineNumber>
      <Department DepartmentName="DepartmentName3" />          <Department DepartmentName="DepartmentName3" />
    </DealLine>                                                                       </DealLine>
    <DealLine>                                                                        <DealLine>
      <DealLineId>2 </DealLineId>                                              <DealLineId>52</DealLineId>
      <DealLineNumber> XYZ2</DealLineNumber>                         <DealLineNumber> XYZ2-2</DealLineNumber>
      <Department DepartmentName="DepartmentName2" />          <Department DepartmentName="DepartmentName42" />
    </DealLine>                                                                       </DealLine>
  </DealLines>                                                                        </DealLines>
</Deal>                                                                              </Deal>


Wednesday, November 23, 2016 - 9:50:31 AM - JazzKatua Back To Top

Very clear tutorial; the screenshots helped a lot!

Tuesday, November 17, 2015 - 6:09:50 PM - Steve LeBlanc Back To Top

I was given a task to modify live email addresses in the middle of am XML stored in an sql table.  These addresses appeared in our test environment which was causing havic with the live users and title offices.  I spent time looking at other sites, saw some examples but no data or explaination of what the out come was to be.

Your site gave me everything I needed, great examples, before look, after look.  I accomplished what I needed.


Thank You

Steve leBlanc

Tuesday, August 18, 2015 - 2:19:37 AM - Madan Back To Top


Thanks for your Great Posts!

This site is really help to resolve my xml issues.

Wednesday, April 29, 2015 - 2:15:19 PM - Seth Back To Top

@Abilash - the modify() method is limited to updating a single node's value at a time.  Once way to accomplish what you want to do is to put the update in a loop:

DECLARE @pos INT = 1;

WHILE (SELECT Salaries.exist('(/Salaries//Salary/text())[sql:variable("@pos")]') FROM HR_XML) = 1



SET Salaries.modify('replace value of ((/Salaries//Salary/text())[sql:variable("@pos")])[1] with ("8000")');


SET @pos +=1;



Wednesday, April 01, 2015 - 2:36:29 AM - Abilash Back To Top

Hi,  These tips are very useful and it has help me in updating the XMLs individual attributes.


But I have one query , Is there any way that we can update all the salaries to 8000 irrespective of them being in accounting or marketing. Currently am able to do this by preperaing multiple update scripts. But Instead can we have one scripts where I can update all at one shot irrespective of department and employee ID.

Friday, January 30, 2015 - 12:52:36 PM - Vincy Back To Top

@Seth, never mind about my previous question.


I have found the solutioin already and it works just fine.  Here is the solution for those who needs it for bulk updates:

DECLARE @setting AS Table
    ( ModuleId VARCHAR(255),
        SiteId VARCHAR(255),
        SettingValue XML
    ( SiteId VARCHAR(255),
        AdmitterUserId VARCHAR(100)

INSERT INTO @data (SiteId, AdmitterUserId)
SELECT '0046DB9E-40BC-4961-B089-18575C7E65C1', 'zeus'
SELECT '7CF5BEE4-73EE-4DA2-8A38-393A7E118EE5', 'vincy'
INSERT INTO @setting (ModuleId, SiteId, SettingValue)    
select ModuleId, SiteId, CAST(Settings AS XML) from ModuleSiteSettings
where ModuleId='EdThroughput'   
AND SiteId in (SELECT SiteId FROM @data)

SELECT * FROM @setting

SET SettingValue.modify('replace value of (/Settings/AdmitterUserId/text())[1] with (sql:column("AdmitterUserId"))')
    FROM @setting AS s
        INNER JOIN @data AS d ON s.SiteId = d.SiteId

SELECT * FROM @setting

Monday, October 13, 2014 - 10:11:29 PM - Arkitec Back To Top

I have been banging my head against both inserting and updating xml for many hours.  Your article with the very clear examples solved my problems in about 15 minutes.


Wednesday, July 09, 2014 - 10:18:39 AM - rakesh Back To Top

can we raplace value of a particuler node with another column value of same table


i have a table having 2 columns.(id as int,xml as xml)

xml structure below:





not want to replace data tag value with id value


thnx in advace

Monday, June 23, 2014 - 4:29:13 AM - VoDucGiap Back To Top

Thank you .

Monday, May 19, 2014 - 9:13:36 AM - Seth Delconte Back To Top

@Prem - please see the 'Replacing Values' section of this article.  You'll want to use the 'replace value of' expression inside of the modify function.

Sunday, May 18, 2014 - 10:50:19 PM - prem Back To Top

I want to replace the code which has alpha numeric characters as alpha characters, how can we do it?



<Code>Cas234p</Code> --should display as Casp



Friday, April 04, 2014 - 9:13:50 AM - Marcelo Back To Top

bookmarked! Thank you

Friday, January 24, 2014 - 1:48:40 AM - Yadnesh Back To Top

Hi Seth,

In our project we are using XML database and storing XML values in XML columns. for example for deal and Deal lines the XML will be as given below.

&lt;DealNumber&gt; 1001&lt;/DealNumber&gt;
      &lt;DealLineId&gt;1 &lt;DealLineId&gt;
       &lt;DealLineNumber&gt;ABC1 &lt;DealLineNumber&gt;
   &lt;Department DepartmentName="DepartmentName3"/&gt;
      &lt;DealLineId&gt;2 &lt;DealLineId&gt;
       &lt;DealLineNumber&gt; XYZ2&lt;DealLineNumber&gt;
   &lt;Department DepartmentName="DepartmentName2"/&gt;

1 deal may contain around 15000 Deal Lines so the XML for 1 deal is of very large size.

Problem arises when we have to update any node value for all 1500 Deal lines. As Replace Value Of updates only 1 node at once we have to apply loop for updating all nodes. This takes quite a long time. We tried creating Primary and secondary indexes but that doesn't improve update speed.

Is there any other way to do bulk update than using Replace value Of in XQuery?


Tuesday, February 05, 2013 - 1:28:43 PM - Seth Back To Top


You should be storing XML fragments in XML fields, not NTEXT.   To work around your situation, however, you can select the NTEXT into an XML variable, perform the XML insert on the variable, then replace the original NTEXT field's contents with the XML variable's:

--create XML variable to hold fragment


--convert the NTEXT to XML

SELECT @xml = CONVERT(XML, formxml)

FROM tblmemberforms

WHERE formxml like '%<field name="chkpreferredpharmacyYes"><value>X</value>%' AND memberid = @MemberID

--perform the XML insert

SET @xml.modify('insert sql:variable("@allNode") before (/fields/field)[1]')

--replace the previous contents of the NTEXT column with the newly updated XML (converting back to text)

UPDATE tblmemberforms

SET formxml = CONVERT(VARCHAR(MAX), @xml)

WHERE formxml like '%<field name="chkpreferredpharmacyYes"><value>X</value>%' AND memberid = @MemberID

Tuesday, February 05, 2013 - 5:29:15 AM - Suma Back To Top





UPDATE tblmemberforms


cast(formxml as xml).modify('insert sql:variable("@allNode")

before (/fields/field)[1]') 

and formxml like '%<field name="chkpreferredpharmacyYes"><value>X</value>%' AND memberid = @MemberID

Here it is not allowing me to use cast statement while using modify method.


I have to cast ntext to xml and then apply modify


Pls suggest me with alternative

Friday, August 03, 2012 - 8:54:25 AM - shahid Back To Top

nice little tip, do you think you could cover generating schema from your xml file, i use an xml schema editor but manual instructions would help me learn faster.

Thursday, August 02, 2012 - 10:44:05 AM - Armando Prato Back To Top

I concur, terrific tip

Thursday, August 02, 2012 - 9:34:08 AM - JohnF Back To Top
Thank you for so many examples! This will be bookmarked!

Learn more about SQL Server tools