SQLServerCentral Article

XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

,

Introduction

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

<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@Team)[2]
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server" />
  <Employee name="Steve" city="FL" />
  <Employee name="Bob" city="CA" Team="ASP.NET" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee[position()=2]/@Team)
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server" />
  <Employee name="Steve" city="FL" />
  <Employee name="Bob" city="CA" Team="ASP.NET" />
</Employees>
*/

And here is a third option.

Example 4

DECLARE @x XML
SELECT @x = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee[2]/@Team)
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server" />
  <Employee name="Steve" city="FL" />
  <Employee name="Bob" city="CA" Team="ASP.NET" />
</Employees>
*/

The following example deletes the "Team" attribute from the "last" employee element.

Example 5

DECLARE @x XML
SELECT @x = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee[position()=last()]/@Team)
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server" />
  <Employee name="Steve" city="FL" Team="SQL Server" />
  <Employee name="Bob" city="CA" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@Team)
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" />
  <Employee name="Steve" city="FL" />
  <Employee name="Bob" city="CA" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@Team[.="SQL Server"])
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" />
  <Employee name="Steve" city="FL" />
  <Employee name="Bob" city="CA" Team="ASP.NET" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@Team[.!="SQL Server"])
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server" />
  <Employee name="Steve" city="FL" Team="SQL Server" />
  <Employee name="Bob" city="CA" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@Team[.="SQL Server" or .="ASP.NET"])
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" />
  <Employee name="Steve" city="FL" />
  <Employee name="Bob" city="CA" />
</Employees>
*/

The following example deletes the "city" attribute from all "Employee" elements where the "Team" is "SQL Server".

Example 10

DECLARE @x XML
SELECT @x = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee[@Team="SQL Server"]/@city)
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" Team="SQL Server" />
  <Employee name="Steve" Team="SQL Server" />
  <Employee name="Bob" city="CA" Team="ASP.NET" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@*)
    ')
SELECT @x 
/*
<Employees>
  <Employee />
  <Employee />
  <Employee />
</Employees>
*/

The following example deletes all the attributes of elements where the "Team" is "SQL Server"

Example 12

DECLARE @x XML
SELECT @x = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee name="Steve" city="FL" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee[@Team="SQL Server"]/@*)
    ')
SELECT @x 
/*
<Employees>
  <Employee />
  <Employee />
  <Employee name="Bob" city="CA" Team="ASP.NET" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee city="FL" name="Steve" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@*[position()=1])
    ')
SELECT @x 
/*
<Employees>
  <Employee city="NY" Team="SQL Server" />
  <Employee name="Steve" Team="SQL Server" />
  <Employee city="CA" Team="ASP.NET" />
</Employees>
*/

The following example deletes the last attribute from all the "Employee" elements.

Example 14

DECLARE @x XML
SELECT @x = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee city="FL" name="Steve" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@*[position()=last()])
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" />
  <Employee city="FL" name="Steve" />
  <Employee name="Bob" city="CA" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee city="FL"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@*[position()=last()])
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" />
  <Employee />
  <Employee name="Bob" city="CA" />
</Employees>
*/

The following example deletes the last attribute from all "Employee" elements of "SQL Server" Team.

Example 16

DECLARE @x XML
SELECT @x = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee city="FL" name="Steve" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee[@Team="SQL Server"]/@*[position()=last()])
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" />
  <Employee city="FL" name="Steve" />
  <Employee name="Bob" city="CA" Team="ASP.NET" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee city="FL" name="Steve" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee[@Team="SQL Server"][position()=last()]/@*[position()=last()])
    ')
SELECT @x 
/*
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server" />
  <Employee city="FL" name="Steve" />
  <Employee name="Bob" city="CA" Team="ASP.NET" />
</Employees>
*/

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 = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee city="FL" name="Steve" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@*[position()=1 and local-name()="name"])
    ')
SELECT @x 
/*
<Employees>
  <Employee city="NY" Team="SQL Server" />
  <Employee city="FL" name="Steve" Team="SQL Server" />
  <Employee city="CA" Team="ASP.NET" />
</Employees>
*/

Here is another way of writing the same code.

Example 19

DECLARE @x XML
SELECT @x = '
<Employees>
  <Employee name="Jacob" city="NY" Team="SQL Server"/>
  <Employee city="FL" name="Steve" Team="SQL Server"/>
  <Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'
SET @x.modify('
    delete (Employees/Employee/@*[position()=1][local-name()="name"])
    ')
SELECT @x 
/*
<Employees>
  <Employee city="NY" Team="SQL Server" />
  <Employee city="FL" name="Steve" Team="SQL Server" />
  <Employee city="CA" Team="ASP.NET" />
</Employees>
*/

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.

Additional reading: SQL Server XQuery Tutorials

About the author

Jacob Sebastian is a SQL Server MVP and blogs regulary at BeyondRelational.com on SQL Server relational and non-relational topics. He can be reached at jacob@beyondrelational.com

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.95 (21)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.95 (21)

You rated this post out of 5. Change rating

Related content