Modifying some xml

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  January 21, 2011 at 10:02 am

  #232526

  Thanks for your help in advance.

  I am trying to edit some xml

  <resRefDate>

  <refDate>20121218</refDate>

  <refDateType>

  <DateTypCd value="001" />

  </refDateType>

  </resRefDate>

  <resRefDate>

  <refDate>19820116</refDate>

  <refDateType>

  <DateTypCd value="002" />

  </refDateType>

  </resRefDate>

  UPDATE dbo._SSA SET xml_data.modify('replace value of (/resRefDate/refDate/text())[1] with xs:string(sql:variable("@dTxtPubdate")) ')

  UPDATE dbo._SSA SET xml_data.modify('replace value of (/resRefDate/refDate/text())[2] with xs:string(sql:variable("@dTxtPubdate")) ')

  At the moment the first refDate updates correctly but not the second. Could you please help with how I need to alter the modify so that I can change the values for both nodes.

  Any help you can offer would be great Many Thanks,

  Oliver

• Red8Rain

  Hall of Fame

  Points: 3312

  More actions

  January 21, 2011 at 7:18 pm

  #1276858

  i don't know how you would do it in SQL but here's how you can do it in Powershell

  $filexml = gc 'C:\Users\a\sc.xml'

  [xml]$file = $filexml

  $newData = 'xs:string(sql:variable("@dTxtPubdate")'

  $file.SelectSingleNode("resRefDate/refDate")."#text" = $newData

  $file.Save("C:\Users\a\sc.xml")

  Also you have two root node in your xml file so you will either need to get rid of one or combine them

  -----------------------------
  www.cbtr.net
  .: SQL Backup Admin Tool[/url] :.

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  January 24, 2011 at 4:03 am

  #1277109

  thanks for the help, changed the code to this and success.

  Many Thanks,

  Oliver

  UPDATE dbo._SSA SET xml_data.modify('replace value of (/resRefDate[1]/refDate/text())[1] with xs:string(sql:variable("@dTxtPubdate")) ')

  UPDATE dbo._SSA SET xml_data.modify('replace value of (/resRefDate[2]/refDate/text())[1] with xs:string(sql:variable("@dTxtPubdate")) ')