Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

By Jacob Sebastian,

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

Total article views: 6557 | Views in the last 30 days: 5
 
Related Articles
FORUM

Triggers Examples

Triggers Examples

BLOG

Master Data Services (MDS): Bug deleting an attribute

UPDATE on 11/28/2012:  I had this same error when trying to delete a model.  Fortunately, SQL Server...

FORUM

file attributes

File attributes

BLOG

SQL Server Table Partition Example

TwitterGoogle+The post SQL Server Table Partition Example appeared first on Derek Wilson - Blog.

FORUM

connection attributes

view attributes of existing connections

Tags
sebastian    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones