replace in xml type

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  December 8, 2013 at 2:55 am

  #283385

  hello all.

  I have one xml column in my tables:

  and it's xml:

  <SystemProfile>

  <CardTable>

  <Reminder TimeOut="300" isActive="1" />

  <Recycle RemovePeriodTime="60" />

  <Message RemovePeriodTime="60" />

  <Outbox>

  <ConsiderNow authorizedCount="" />

  <Considered authorizedCount="" />

  <NotConsidered authorizedCount="" />

  <Reject authorizedCount="" />

  </Outbox>

  </CardTable>

  <Page>

  <DocFlowHistoryView default="Graph" />

  <SendService viewInOutbox="1" searchItem="role" searchOption="include" />

  <Theme default="silver" />

  </Page>

  <SecurityPolicies>

  <UserAccountStructure minPassLen="-1" maxPassLen="-1" complexRule="-1" />

  <ChangeAccountInFirstLogin value="-1" />

  <MaxUserAccountAge value="-1" />

  <PasswordExpirationAge value="-1" />

  </SecurityPolicies>

  </SystemProfile>

  I need to replace <Theme default="silver" /> with <Theme default="classic" /> and use this script:

  select [SystemProfile].modify('

  replace value of (//SystemProfile/Page/Theme[text()="silver"]/text())[1]

  with "clasic" ')

  from [Users]

  but I have some mistake.please guid me.thanks

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  December 8, 2013 at 9:03 am

  #1671757

  This code works

  DECLARE @x XML

  SELECT @x = '

  <SystemProfile>

  <CardTable>

  <Reminder TimeOut="300" isActive="1" />

  <Recycle RemovePeriodTime="60" />

  <Message RemovePeriodTime="60" />

  <Outbox>

  <ConsiderNow authorizedCount="" />

  <Considered authorizedCount="" />

  <NotConsidered authorizedCount="" />

  <Reject authorizedCount="" />

  </Outbox>

  </CardTable>

  <Page>

  <DocFlowHistoryView default="Graph" />

  <SendService viewInOutbox="1" searchItem="role" searchOption="include" />

  <Theme default="silver" />

  </Page>

  <SecurityPolicies>

  <UserAccountStructure minPassLen="-1" maxPassLen="-1" complexRule="-1" />

  <ChangeAccountInFirstLogin value="-1" />

  <MaxUserAccountAge value="-1" />

  <PasswordExpirationAge value="-1" />

  </SecurityPolicies>

  </SystemProfile>'

  set @x.modify('

  delete (//SystemProfile/Page/Theme)[1]')

  set @x.modify('

  insert ( <Theme default="clasic" />)

  into (/SystemProfile/Page)[1]

  ')

  select @x

  I think you should first get the value from the xml type column into a xml variable, update the variable and then make an update for the appropriate row id.

  Regards,

  IgorMi

  Igor Micev,My blog: www.igormicev.com