Jacob's Blog

One of the readers recently asked me if we could delete an attribute with a specific name from all the elements of an XML document. However, the deletion should occur only if the attribute is at a given position.

Here is the sample data for this Lab.

<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>

The task is the following: Delete the “name” attribute from all the elements where “name” is the first attribute in the element. The first and third element has “name” as the first attribute. After the delete operation, the “name” attribute should be removed from the first and third element, but the second element should retain the attribute.

Here is the query that performs this operation.

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>
*/

I will be at the Delhi PASS Chapter Launch event by SQLBuzzDelhi on 11th July 2009, along with Pinal Dave, the SQL Server Wizard of India.

I will be very glad to meet many of you at the venue. Feel free to come forward and meet us if you see us moving around :-). Make sure that you don’t miss Pinal’s session on SQL Server Best Practices.

During my session, I will explain the logical execution flow of a query, which will give a clear idea of how a query is processed and executed. A good understanding of the query execution flow will help us to answer many of the questions that we face in our daily database programming life and will help us to write better TSQL queries. In addition, those of you interested in grabbing stickers of tsqlchallenges.com can come forward and collect it from me.

I will give away two copies of my recent book, “The Art of XSD – SQL Server XML Schema Collections” to the most active participants.

So make sure that you are on time and don’t fall asleep during the sessions. If you have not yet registered for the event, use the following link to book your seats: http://mtcarmeltechedonroads.eventbrite.com/