How to update all attributes’ value of your XML data column using T-SQL

How did I know this?

Let’s have a sample snippet

   1: DECLARE @xml XML = N'

   2: <a abb="122">

   3:     <b>

   4:     </b>

   5: </a>

   6: <a abb="344">

   7:     <b>

   8:     </b>

   9: </a>

  10: '

We need ​​to update all attribute ‘abb’ of ‘a’ tag with value 888 We can do the following:

   1: set @xml.modify('replace value of (/a/@abb)[1] with 888')

   2: set @xml.modify('replace value of (/a/@abb)[2] with 888')

By doing so we can solve the above requirement. But in  such case, the data doesn’t have 2 ‘a’ tag, i.e there are > 2 positions to update, that doesn’t help work. We must have  a different way which can deal with a dynamic number N of  ‘a’ tags, i.e we don’t  know N value before. The idea to do that is the following: Split the XML ‘a’ tag into rows of  a temp table (e.g. a table variable), replace each node separately and then combine them again.

   1: --  declare variable @T type table 

   2: declare @T table (XMLCol xml)


   4: -- spilit xml insert to table

   5: insert into @T

   6: select


   8: from @xml.nodes('a') as XMLTable(data)


  10: -- Update all rows 

  11: update @T set

  12:   XMLCol.modify(N'

  13:                 replace value of (/a/@abb)[1]

  14:                 with 888

  15:         ')


  17: -- Combine all rows into xml

  18: set @xml = (select XMLCol as [*]

  19:             from @T

  20:             for xml path(''))

Download demo here

Hope that this post is useful for you!

Nhu & Nam.


One response to this post.

  1. Posted by LUke on August 26, 2011 at 9:07 AM



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: