Use of xml.modify from nested XML - SSRS Subscription Table

Question

I'm trying to do a bulk update on email addresses stored in the ExtensionSettings columns from the Subscription table in SSRS database.

I'm a bit lost on how to update the TO and CC values from the XML column.

<ParameterValues>
    <ParameterValue>
        <Name>TO</Name>
        <Value>[email protected]</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>CC</Name>
        <Value>[email protected]</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>BCC</Name>
        <Value />
    </ParameterValue>
    <ParameterValue>
        <Name>ReplyTo</Name>
        <Value />
    </ParameterValue>
    <ParameterValue>
        <Name>Subject</Name>
        <Value>@ReportName was executed at @ExecutionTime</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>Comment</Name>
        <Value />
    </ParameterValue>
    <ParameterValue>
        <Name>Priority</Name>
        <Value>NORMAL</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>IncludeLink</Name>
        <Value>True</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>IncludeReport</Name>
        <Value>True</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>RenderFormat</Name>
        <Value>MHTML</Value>
    </ParameterValue>
</ParameterValues>

I got some reference but still lost

https://www.sqlshack.com/different-ways-to-update-xml-using-xquery-in-sql-server/

Basically, I just want to update those properties with < Name >TO</ Name> and < Name >CC</ Name> and update the values to "[email protected]"

UPDATE user_details
SET ExtensionSettings.modify('replace value of (/ParameterValues/ParameterValue/Value/text()) with "[email protected]"')
WHERE SubscriptionID='2B0080DE-14FA-4A3D-8AEF-14A9238E7841'

Answer 1

The XML.modify() method will only allow you to perform a single modification at a time. Since you want to modify two values you need to execute two UDPATE statements:

UPDATE user_details
SET ExtensionSettings.modify('
  replace value of (/ParameterValues/ParameterValue[Name/text()="TO"]/Value/text())[1]
  with "[email protected]"
')
WHERE SubscriptionID='2B0080DE-14FA-4A3D-8AEF-14A9238E7841';

UPDATE user_details
SET ExtensionSettings.modify('
  replace value of (/ParameterValues/ParameterValue[Name/text()="CC"]/Value/text())[1]
  with "[email protected]"
')
WHERE SubscriptionID='2B0080DE-14FA-4A3D-8AEF-14A9238E7841';

Selecting the resultant XML will return:

<ParameterValues>
    <ParameterValue>
        <Name>TO</Name>
        <Value>[email protected]</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>CC</Name>
        <Value>[email protected]</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>BCC</Name>
        <Value />
    </ParameterValue>
    <ParameterValue>
        <Name>ReplyTo</Name>
        <Value />
    </ParameterValue>
    <ParameterValue>
        <Name>Subject</Name>
        <Value>@ReportName was executed at @ExecutionTime</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>Comment</Name>
        <Value />
    </ParameterValue>
    <ParameterValue>
        <Name>Priority</Name>
        <Value>NORMAL</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>IncludeLink</Name>
        <Value>True</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>IncludeReport</Name>
        <Value>True</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>RenderFormat</Name>
        <Value>MHTML</Value>
    </ParameterValue>
</ParameterValues>