Updating varchar(max) column

  • 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

  • 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

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

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

    xml =

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

  • 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

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

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