Updating varchar(max) column

Question

Updating varchar(max) column

ishaan99

SSCertifiable

Points: 6261
More actions
March 4, 2009 at 11:29 am

#135145

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

beezell Hall of Fame Points: 3112 More actions · Answer 1

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

ishaan99 SSCertifiable Points: 6261 More actions · Answer 2

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

ishaan99 SSCertifiable Points: 6261 More actions · Answer 3

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

xml =

ishaan99 SSCertifiable Points: 6261 More actions · Answer 4

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.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

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

Here a little example:

DECLARE @v-2 VARCHAR(MAX)

SET @v-2 = '<Root><Age>45</Age></Root>'

DECLARE @xml XML

SET @xml = @v-2

SET @xml.modify('

replace value of (/Root/Age/text())[1]

with "20"

')

select @xml

Greets

Flo