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>Jon@yahoo.com</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>CC</Name>
        <Value>Sarah@yahoo.com</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 "Test@domain.com"

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

score 0 · Answer 1 · answered Feb 09 '23 at 09:20

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 "Test@domain.com"
')
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 "Test@domain.com"
')
WHERE SubscriptionID='2B0080DE-14FA-4A3D-8AEF-14A9238E7841';

Selecting the resultant XML will return:

<ParameterValues>
    <ParameterValue>
        <Name>TO</Name>
        <Value>Test@domain.com</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>CC</Name>
        <Value>Test@domain.com</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>

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

1 Answers1