Update Node Value XML T-SQL Based on Value of Other Node at Same Level

Question

I would like to update a piece of xml, which I hold in an variable. I would like to update the value of Value where Name is a given value. Here's a mockup of the xml:

<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>[email protected]</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>[email protected]</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Priority</Name>
    <Value>NORMAL</Value>
  </ParameterValue>
</ParameterValues>

I have written the below, which always updates the first value, but I'd like to change the modify command so that I have the equivalent to a where clause - I would like to update (for instance) the value where Name = "CC" in the same node. How do I achieve this?

All the examples I see online assume I have a unique identifier for each node in the tree structure above the value I want to update, not at the same level.

DECLARE @Email VARCHAR(50) = '[email protected]';
DECLARE @Ext XML =
'<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>[email protected]</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>[email protected]</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Priority</Name>
    <Value>NORMAL</Value>
  </ParameterValue>
</ParameterValues>';

SET @Ext.modify('replace value of (//Value/text())[1] with sql:variable("@Email")');

SELECT
     Col.value('./Name[1]','VARCHAR(100)')  AS [Name]
    ,Col.value('./Value[1]','VARCHAR(1000)')AS [Value]
FROM
    @Ext.nodes('//ParameterValue')  AS Tab(Col);

All help gratefully received!

Gottfried Lesigang · Accepted Answer · 2019-06-11 12:17:47Z

1

Try it like this

DECLARE @Ext XML =
'<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>[email protected]</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>[email protected]</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Priority</Name>
    <Value>NORMAL</Value>
  </ParameterValue>
</ParameterValues>';

DECLARE @Email VARCHAR(50) = '[email protected]';
DECLARE @SearchFor VARCHAR(100)='CC'

SET @Ext.modify('replace value of (/ParameterValues/ParameterValue[Name=sql:variable("@Searchfor")]/Value/text())[1] with sql:variable("@Email")');

SELECT @Ext;

The idea in short.

The .modify() method can change only one place per call. That means, that the first argument (the XPath after replace value of) must be a singleton. We need a second variable to define which parameter we want to change. Here I use:

replace value of (/ParameterValues
                 /ParameterValue[Name=sql:variable("@Searchfor")]
                 /Value/text())[1] 
with sql:variable("@Email")

You can read this as Dive into <ParameterValues>, then into <ParameterValue> and find one with the given Name. Below we dive into <Value> and return the text().

If there are multiple occurances we'll pick the first in any case and replace it with the given value.

edited Jun 11, 2019 at 12:17

answered Jun 11, 2019 at 12:09

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

2 Comments

High Plains Grifter Over a year ago

I like the use of the sql variable in the address here and have taken your comment about poor performance of the [../Name="CC"] syntax on the other answer into account.

Gottfried Lesigang Over a year ago

@HighPlainsGrifter Some background on the ../ by Mikael Eriksson

Paweł Dyl · Accepted Answer · 2019-06-11 12:07:44Z

1

See following snippet:

SET @Ext.modify('replace value of (//Value[../Name="CC"]/text())[1] with sql:variable("@Email")');

The trick is to apply condition in square brackets.

answered Jun 11, 2019 at 12:07

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

2 Comments

Gottfried Lesigang Over a year ago

This works, but is very bad in performance (at least with bigger XMLs). The backward navigation (parent-axis, ../) is a known performance killer... Furthermore, the // will trigger the deep search. This will have to find each and any <Value> anywhere in the XML and test its parent for a <Name>...

High Plains Grifter Over a year ago

All for the want of square brackets! Damnable syntax!

Collectives™ on Stack Overflow

Update Node Value XML T-SQL Based on Value of Other Node at Same Level

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related