Most of the articles I presented in the XML Workshop were either generating XML documents or querying XML documents. An area that I have not touched in detail in this series of articles is modifying XML documents. I am dedicating this and the next few sessions to discuss various scenarios of modifying XML documents.
Let us start with deleting elements and attributes from XML documents. We will then discuss INSERT operation and will see how to modify elements and attributes. We will also see how to re-structure XML documents (formatting from one structure to another). The syntax to perform modifications on XML elements and attributes are very close, but it is found to be very confusing to many people. To make this less confusing, I will cover elements and attributes separately.
I have presented an example of deleting an attribute from an XML document in the XQuery Tutorial series that I wrote in my blog. You can find the post here. My blog post shows a basic example of deleting an attribute from an XML document. In this session, we will see close to 2 dozen examples demonstrating different scenarios.
Sample XML Document
Here is the sample XML document that we will use for the examples in this session.
Example 1
Deleting an attribute of an element at a given position
The following example deletes the "Team" attribute from the second "Employee" element
Example 2
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@Team)[2]
')
SELECT @x
/*
*/
The following example shows another way of writing the same query. The example below uses the position() function.
Example 3
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee[position()=2]/@Team)
')
SELECT @x
/*
*/
And here is a third option.
Example 4
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee[2]/@Team)
')
SELECT @x
/*
*/
The following example deletes the "Team" attribute from the "last" employee element.
Example 5
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee[position()=last()]/@Team)
')
SELECT @x
/*
*/
Deleting an attribute from all the elements
The following example deletes the "Team" attribute from all the "Employee" elements.
Example 6
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@Team)
')
SELECT @x
/*
*/
Deleting attributes containing specific values
The following example deletes the "Team" attribute from all elements where the value of the attribute is "SQL Server".
Example 7
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@Team[.="SQL Server"])
')
SELECT @x
/*
*/
The following example uses a 'not equal to' condition. It deletes the "Team" attribute from all elements where the value is not "SQL Server".
Example 8
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@Team[.!="SQL Server"])
')
SELECT @x
/*
*/
The next example uses an "or" operator to evaluate multiple comparison operations. It deletes the "Team" attribute from all elements where the value of the "Team" attribute is "SQL Server" or "ASP.NET"
Example 9
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@Team[.="SQL Server" or .="ASP.NET"])
')
SELECT @x
/*
*/
The following example deletes the "city" attribute from all "Employee" elements where the "Team" is "SQL Server".
Example 10
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee[@Team="SQL Server"]/@city)
')
SELECT @x
/*
*/
Deleting all attributes
The following examples demonstrate how to delete all the attributes of one or more elements. The first example given below deletes all the attributes of all employee elements.
Example 11
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@*)
')
SELECT @x
/*
*/
The following example deletes all the attributes of elements where the "Team" is "SQL Server"
Example 12
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee[@Team="SQL Server"]/@*)
')
SELECT @x
/*
*/
Deleting attributes by position
The following example deletes the first attribute from all "employee" elements. Note that the order of attributes is not significant in XML. The code below deletes the first attribute by position. Since the deletion is performed using the physical order of the attributes, the first and last elements looses the "name" attribute and the second element looses the "city" attribute.
Example 13
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@*[position()=1])
')
SELECT @x
/*
*/
The following example deletes the last attribute from all the "Employee" elements.
Example 14
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@*[position()=last()])
')
SELECT @x
/*
*/
Look at the following example. The second "Employee" element has only one attribute. If we run the same delete statement as the above, the first and last elements will loose the 3rd attribute and the second element will loose the first attribute (because it is the only attribute that the element has)
Example 15
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@*[position()=last()])
')
SELECT @x
/*
*/
The following example deletes the last attribute from all "Employee" elements of "SQL Server" Team.
Example 16
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee[@Team="SQL Server"]/@*[position()=last()])
')
SELECT @x
/*
*/
The following example deletes the last attribute from the last element from "SQL Server" Team (second element in the example)
Example 17
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee[@Team="SQL Server"][position()=last()]/@*[position()=last()])
')
SELECT @x
/*
*/
The following example deletes the first attribute from all "Employee" elements if the name of the attribute is "name".
Example 18
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@*[position()=1 and local-name()="name"])
')
SELECT @x
/*
*/
Here is another way of writing the same code.
Example 19
DECLARE @x XML
SELECT @x = '
SET @x.modify('
delete (Employees/Employee/@*[position()=1][local-name()="name"])
')
SELECT @x
/*
*/
Conclusions
In this article, I tried to cover almost all scenarios of deleting attributes, that I could quickly think of. If you have some scenarios that are not listed here, do let me know and I will try to add them to the list.
All the examples we saw in this session use the "modify()" method of XML data type. Another way of doing this is by using a FLOWR operation. I will explain it in another article.