Update UnTyped XML data column in SQL Server 2008/2005

Now a day, XML is booming, it is welcomed from every platform so why Microsoft and especially SQL Server sit back? After SQL Server 2005, Microsoft have provided so many functionality for make developer’s life easy who are using XML. Today, I would like you to show how you can update un-typed XML data from within well known UPDATE T-SQL statement.
–create table for testing purpose with XML column
–in SQL Server 2005 or in 2008
create table xmlTest
(
ColumnXML xml
)
GO
–insert data in XML column
insert into xmlTest
select

 <Orders>  
  <OrderID>A1000</OrderID>  
  <ClientName>Ritesh Shah</ClientName>  
  <Product>HP Notebook</Product>  
</Orders>
GO
–check the output
select * from xmlTest
GO
–update value (un-typed) in product tag
DECLARE @Val varchar(50) 
SELECT @Val = ‘Dell Inspiron’ 
 
UPDATE xmlTest 
SET ColumnXML.modify(‘replace value of (/Orders/Product/text())[1] with sql:variable(“@Val”)’) 
GO
–confirm changes
select * from xmlTest



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Advertisements

2 Responses to “Update UnTyped XML data column in SQL Server 2008/2005”


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: