Examples of using XQuery to update XML Data in SQL Server

https://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

Problem

What is the most efficient way to modify XML values? To allow for precise modification of XML nodes and values, theXQuery 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, anddelete. 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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s