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)

   3:

   4: -- spilit xml insert to table

   5: insert into @T

   6: select

   7:   XMLTable.data.query('.')

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

   9:

  10: -- Update all rows 

  11: update @T set

  12:   XMLCol.modify(N'

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

  14:                 with 888

  15:         ')

  16:

  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.

Advertisements

One response to this post.

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

    Thanks!

    Reply

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: