Updating varchar(max) column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Updating varchar(max) column

Updating varchar(max) column

Rate Topic

Display Mode

Topic Options

Author

Message

ishaan99

Posted Wednesday, March 04, 2009 11:29 AM

Old Hand

Group: General Forum Members
Last Login: Friday, March 30, 2012 9:49 AM
Points: 372, Visits: 269

I have a table which has column age and this column gets used in another column(xml) in the same table.
ex table1
Age varchar(3),
xml varchar(max) - This is an xml column which is a concatenation of many columns in the table and age is one of them

if age = 35
xml = ........(how is it possible to change the 63 to 35) . This is not just one records there are quite a few with different age. so how can i get this in single update than writing multiple updates for each user. ANy help on this wil lbe greatly appreciated
TIA

Post #668537

beezell

Posted Wednesday, March 04, 2009 12:07 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, September 24, 2012 8:10 AM
Points: 2,042, Visits: 499

What exactly are you trying to accomplish? Just running an update on the xml column or creating a trigger so the xml column is updated everytime the age changes? Could you send an example of what the xml column might look like? Is it true xml?

Cheers,
Brian

Post #668571

ishaan99

Posted Wednesday, March 04, 2009 2:03 PM

Old Hand

Group: General Forum Members
Last Login: Friday, March 30, 2012 9:49 AM
Points: 372, Visits: 269

Running an update directly on xml column wont help as its a concatenation of age, dob, firstname, lname and many other columns. something like
if age = 35
xml = ........(how is it possible to change the 63 to 35) .

Post #668682

ishaan99

Posted Wednesday, March 04, 2009 2:06 PM

Old Hand

Group: General Forum Members
Last Login: Friday, March 30, 2012 9:49 AM
Points: 372, Visits: 269

somehow xml data doesnt show up to clarify the questions. its something like
xml =

Post #668685

ishaan99

Posted Wednesday, March 04, 2009 2:30 PM

Old Hand

Group: General Forum Members
Last Login: Friday, March 30, 2012 9:49 AM
Points: 372, Visits: 269

The xml data looks something like this

data id="AGE_CAT"AGE-30/data data id="BMI_CATEGORY"data id="HEIGHT"
instead of 30 is the age column in table is updated to 35 the xml column should reflect this change.

Post #668711

Florian Reischl

Posted Wednesday, March 04, 2009 3:25 PM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

As first I would suggest to change the type of your VARCHAR(MAX) column to XML.

Here a little example:

DECLARE @v VARCHAR(MAX)
SET @v = '<Root><Age>45</Age></Root>'

DECLARE @xml XML
SET @xml = @v

SET @xml.modify('
   replace value of (/Root/Age/text())[1]
   with "20"
   ')

select @xml

Greets
Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Post #668759

« Prev Topic | Next Topic »

Permissions