Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating varchar(max) column Expand / Collapse
Author
Message
Posted Wednesday, March 4, 2009 11:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 19, 2013 11:03 AM
Points: 374, Visits: 280
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
Posted Wednesday, March 4, 2009 12:07 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
Posted Wednesday, March 4, 2009 2:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 19, 2013 11:03 AM
Points: 374, Visits: 280
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
Posted Wednesday, March 4, 2009 2:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 19, 2013 11:03 AM
Points: 374, Visits: 280
somehow xml data doesnt show up to clarify the questions. its something like
xml =



Post #668685
Posted Wednesday, March 4, 2009 2:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 19, 2013 11:03 AM
Points: 374, Visits: 280
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
Posted Wednesday, March 4, 2009 3:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, 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 »

Add to briefcase

Permissions Expand / Collapse