t-sql update XML element value based on another element value

Question

Using SQL Server 2012, if I have the following value in one row of an XML type column:

<ArrayOfScheduledJobParameters xmlns="http://schemas.data.org/ScheduledJobServiceLibrary">
  <ScheduledJobParameters>
    <Key>OutputFileName</Key>
    <Value>CPVR{0}.txt</Value>
  </ScheduledJobParameters>
  <ScheduledJobParameters>
    <Key>DaysBeforeExpiration</Key>
    <Value>60</Value>
  </ScheduledJobParameters>
  <ScheduledJobParameters>
    <Key>MaxItemsByReportServiceCall</Key>
    <Value>100</Value>
  </ScheduledJobParameters>
</ArrayOfScheduledJobParameters>

I want to change the value of the element <Value> based on the value contained in the element <Key>. Specifically I want to be able to set <Value> to 150 where the element <Key> contains the value 'MaxItemsByReportServiceCall'. All that I have been able to do is update the first node of <ScheduledJobParameters>. I can't find how to do it, if it's even possible in any of the documentation I read on XML DML.

Answer 1

You can use function modify() and correct XQuery to set the value that you need. The syntax is quite tricky though:

UPDATE YourTableName
SET ColumnName.modify('declare namespace NS="http://schemas.data.org/ScheduledJobServiceLibrary";replace value of (/NS:ArrayOfScheduledJobParameters/NS:ScheduledJobParameters[NS:Key="MaxItemsByReportServiceCall"]/NS:Value/text())[1] with "150"')
WHERE <your conditions to select rows>

In modify() function you first declare your namespace. Then you use replace value of and correct XQUery. For XQuery you find the ScheduledJobParameters element that has child element name with value MaxItemsByReportServiceCall and select the text of its child element value. Then you use with "new value" to update the field.