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

Attend these FREE SQL Server 2017 webcasts >> click to register


Problem

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.

Solution

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:

CREATE TABLE HR_XML (ID INT IDENTITY, Salaries XML)
GO
INSERT HR_XML VALUES(
    '<Salaries>
      <Marketing>
        <Employee ID="1" tier="4">
          <Salary>42000</Salary>
        </Employee>
        <Employee ID="2" tier="1">
          <Salary>52000</Salary>
        </Employee>
        <Employee ID="3" tier="4">
          <Salary>48000</Salary>
        </Employee>
      </Marketing>
    </Salaries>
    '
)
GO

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

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

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:

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

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:

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

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

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

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:

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

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:

UPDATE HR_XML
SET Salaries.modify('insert <Project ID="1"><Description>Organize new 
    strategies</Description></Project> into 
    (/Salaries/Marketing/Employee[@ID=("2")]/Projects)[1]')
GO

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:

UPDATE HR_XML
SET Salaries.modify('insert 
<Salary>{(/Salaries/Marketing/Employee[@ID=("1")]/Salary/text())}</Salary> 
into (/Salaries/Accounting/Employee[@ID=("4")])[1]')
GO

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:

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

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:

UPDATE HR_XML
SET Salaries.modify('delete 
(/Salaries/Marketing/Employee[@ID=("2")]/Projects/Project[@ID="1"]/Description/text())[1]')
GO

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:

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

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:

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

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 
(/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] 
with ("60000")')
FROM HR_XML
GO

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:

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

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:

UPDATE HR_XML
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")')
GO

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

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

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:

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

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.

Conclusion

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:


signup button

next tip button



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





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 


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.

DECLARE @OrigXml XML;
SET @OrigXml = '
<Deal>
  <DealNumber> 1001</DealNumber>
  <DealLines>
    <DealLine>
      <DealLineId>1 </DealLineId>
      <DealLineNumber>ABC1 </DealLineNumber>
      <Department DepartmentName="DepartmentName3"/>
    </DealLine>
    <DealLine>
      <DealLineId>2 </DealLineId>
      <DealLineNumber> XYZ2</DealLineNumber>
      <Department DepartmentName="DepartmentName2"/>
    </DealLine>
  </DealLines>
</Deal>';

-- 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
DECLARE @NewXML XML;
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

BEGIN

UPDATE HR_XML

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

 

SET @pos +=1;

END

https://social.msdn.microsoft.com/forums/sqlserver/en-US/051626c3-6a80-4079-8128-397d19ed59c5/modifying-more-than-one-node-with-xquery-modify


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
    )
   
DECLARE @data AS TABLE
    ( SiteId VARCHAR(255),
        AdmitterUserId VARCHAR(100)
    )

INSERT INTO @data (SiteId, AdmitterUserId)
SELECT '0046DB9E-40BC-4961-B089-18575C7E65C1', 'zeus'
union
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


UPDATE s
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.

Thanks!


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

e.g

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

xml structure below:

<xml>

<data>0</data>

</xml>

 

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?

<Hospital>

<Assessment>

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

</Assessment>

</Hospital>


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;Deal&gt;
&lt;DealNumber&gt; 1001&lt;/DealNumber&gt;
&lt;DealLines&gt;
   &lt;DealLine&gt;
      &lt;DealLineId&gt;1 &lt;DealLineId&gt;
       &lt;DealLineNumber&gt;ABC1 &lt;DealLineNumber&gt;
   &lt;Department DepartmentName="DepartmentName3"/&gt;
   &lt;DealLine&gt;
   &lt;DealLine&gt;
      &lt;DealLineId&gt;2 &lt;DealLineId&gt;
       &lt;DealLineNumber&gt; XYZ2&lt;DealLineNumber&gt;
   &lt;Department DepartmentName="DepartmentName2"/&gt;
   &lt;DealLine&gt;
&lt;/DealLines&gt;
&lt;/Deal&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

Suma,

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

DECLARE @xml AS XML

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

 

Hi,

 

 

UPDATE tblmemberforms

SET 

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