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.

krivtom · Accepted Answer · 2015-06-09 19:20:02Z

2

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.

answered Jun 9, 2015 at 19:20

krivtom

25k10 gold badges53 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Beneque Over a year ago

@dotnettom I just joined so I cannot vote up yet (need 15 reputation) but I just want to say thank you and agreed that the syntax is not intuitive at all.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related