XQuery

• jagat.patel

  SSC Eights!

  Points: 908

  More actions

  September 29, 2010 at 1:29 pm

  #224562

  Hi all

  I have a XML

  '<_ xmlns="urn:reval-com:rxoap" class="ZeroCurve.CResetTrades" method="resetTradesHistorical" Async="">

  <_Ctrl>

  <UserSysId>1</UserSysId>

  <CompanySysId>1</CompanySysId>

  <ConKey>01</ConKey>

  <CategoryCVid>2</CategoryCVid>

  <TypeCVid>4</TypeCVid>

  <Description>29-Sep-2010 14:45:40 as of 23-Aug-2010 for Index NZD-BBR-FRA for Tenor 1M,2M,3M,4M,5M,6M</Description>

  <PassJobId />

  <Email xmlns="urn:reval-com:Static" SuppressAutoEmail="0">RevalAdmin@reval.com</Email>

  <Message>

  <Operation>Reset Trades Historical_</Operation>

  <Description />

  <_>_has been completed.\</_>

  <_>\</_>

  <Parameters>

  <_ />

  </Parameters>

  </Message>

  </_Ctrl>

  <UsrData>

  <UserSysId>1</UserSysId>

  <CompanySysId>1</CompanySysId>

  <ConKey>01</ConKey>

  </UsrData>

  <_ />

  <_ />

  <_ />

  <_ />

  <_>2010-08-23</_>

  <_>10</_>

  <_>1M,2M,3M,4M,5M,6M</_>

  <_>29-Sep-2010 14:45:40 as of 23-Aug-2010 for Index NZD-BBR-FRA for Tenor 1M,2M,3M,4M,5M,6M</_>

  </_>'

  and need to update Date '2010-08-23' update with '2010-08-24' all all referance to this Date (23-Aug-2010 with 24-Aug-2010)

  and <ConKey>01</ConKey> with <ConKey>02</ConKey>

  i need to do this because I have to ran this for 90 days (I have date list in a table)

  Thanks for your help

  Jagat

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  September 29, 2010 at 2:04 pm

  #1229314

  A simple way is to simply CAST () your xml as VARCHAR(MAX) and then use the REPLACE() function to make the substitutions you want.

  declare @xml xml = 'your example above'

  select @xml

  select @xml = REPLACE(cast(@xml as varchar(max)),'<ConKey>01<','<ConKey>02<')

  select cast(@xml as xml)

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• jagat.patel

  SSC Eights!

  Points: 908

  More actions

  September 29, 2010 at 2:08 pm

  #1229315

  yes but how about dates

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  September 29, 2010 at 2:24 pm

  #1229323

  Dates in XML are just character strings. Everything in XML is just a character string.

  replace(@XML,'23-Aug-2010','24-Aug-2010')

  replace(@XML,'2010-08-23','2010-08-24')

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills