A Patriotic SQL Script

XQuery Lab 2

In our first XQuery lab, we used the query() method to filter XML tags for specific criteria matches.  This time, let’s work on performing DML operations using an XML extension method – modify().

We again need some test data, so let’s create a table and insert a record of XML data:

CREATE TABLE EMPLOYEES (ID INT IDENTITY, Details XML)
GO
INSERT EMPLOYEES VALUES(
 '<Employees>
 <Employee ID="1" salary="49000">
 <Department>Sales</Department>
 </Employee>
 <Employee ID="2" salary="56000" />
 <Employee ID="3" salary="79000" />
 </Employees>
 '
)
GO

We now have one record of Employee details, in the form of an XML BLOB, in the Employees table:

Notice that not all employees have a Department node.  Let’s use the insert…into keywords to insert a node for employee #2:

UPDATE EMPLOYEES
SET Details.modify('insert <Department>Development</Department> into 
(/Employees/Employee[@ID=("2")])[1]')
GO

We have created a new Department node for employee #2:

We can also use the modify() method to replace values using the replace value of keywords.  Employee #1 should not be a member of the Sales department.  Let’s replace the Department node value with “Quality Assurance”:

UPDATE EMPLOYEES
SET Details.modify('replace value of 
(/Employees/Employee[@ID=("1")]/Department/text())[1] with ("Quality Assurance")')
GO

The Department node’s value has been replaced:

The modify() method also accommodates deleting nodes.  Let’s use it to remove the Employee node for employee #3:

UPDATE EMPLOYEES
SET Details.modify('delete (/Employees/Employee[@ID=("3")])[1]')
GO

The Employee node has been removed:

You may have noticed the singleton designation (‘[1]’) we’ve used at the end of every node location specification.  This is required for the insert and replace value of keywords, to indicate which node instance will receive the change, even if there is only one instance of the node.  We did not need to use the singleton in our delete operation, as there was only one Employee node having an ID of 3.  Delete will remove the first instance that matches the criteria; the singleton is not required.

 

[XQuery Lab 1]