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>me@you.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>bob@email.com</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) = 'New@email.co.uk';
DECLARE @Ext XML =
'<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>me@you.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>bob@email.com</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!

score 1 · Answer 1 · answered Jun 11 '19 at 12:07

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 '19 at 12:07

Paweł Dyl

8,888
1
11
27

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 `` anywhere in the XML and test its parent for a ``... – Shnugo Jun 11 '19 at 12:14
All for the want of square brackets! Damnable syntax! – High Plains Grifter Jun 11 '19 at 12:14

Shnugo · Accepted Answer · 2019-06-11T12:17:47.370

Try it like this

DECLARE @Ext XML =
'<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>me@you.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>bob@email.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Priority</Name>
    <Value>NORMAL</Value>
  </ParameterValue>
</ParameterValues>';

DECLARE @Email VARCHAR(50) = 'New@email.co.uk';
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.

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. — High Plains Grifter, Jun 11 '19 at 12:18
@HighPlainsGrifter [Some background on the `../` by Mikael Eriksson](https://stackoverflow.com/questions/24196516/cross-apply-xml-query-performs-exponentially-worse-as-xml-document-grows/24199428#24199428) — Shnugo, Jun 11 '19 at 12:20

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

2 Answers2