How to modify an xml variable with a conditional/where clause

Question

I'm trying to figure out the syntax for modifying the value of an xml variable conditionally. If this were a table, it would be easy because I would just use a WHERE clause to specify which of multiple nodes I want to update. But when all I have is a variable, I use the SET command to do the modify, and that doesn't allow a WHERE clause.

Example:

DECLARE @xml xml = '
<Container>
  <Collection>
    <foo>One</foo>
    <bar>true</bar>
    <baz>false</baz>
  </Collection>
  <Collection>
    <foo>Two</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
  <Collection>
    <foo>Three</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
</Container>
'

SELECT node.value('(foo/text())[1]', 'varchar(10)') AS Item,
       node.value('(bar/text())[1]', 'varchar(10)') AS IsBar,
       node.value('(baz/text())[1]', 'varchar(10)') AS IsBaz
FROM @xml.nodes('/*/Collection') t(node)

So I have two questions I can't seem to figure out the syntax for:

1) I want to modify JUST the 'two' mode so that 'IsBar' is false, while not touching the value of 'IsBar' for the other nodes.

2) I want to, in one statement, update all "IsBar" values to "false".

I can't find the right magic incantation for (1), and for (2) if I try the obvious, I get an error that replace can only update at most one node.

For (1) I've tried this, and it doesn't modify anything (though it doesn't give me any error), so I'm clearly missing something obvious in my pathing:

SET @xml.modify('replace value of ((/*/Collection)[(foo/text())[1] = "Two"]/bar/text())[0] with "false"')

For (2), I want something like this, but it just gives an error:

SET @xml.modify('replace value of (/*/Collection/bar/text()) with "false"')

XQuery [modify()]: The target of 'replace' must be at most one node, found 'text *'

I googled around and simply couldn't find anyone trying to update an xml variable conditionally (or all nodes at once). And frankly, I'm clearly doing something wrong because none of my attempts have ever modified the @xml variable values, so I just need another set of eyes to tell me what I'm getting wrong.

Yitzhak Khabinsky · Accepted Answer · 2019-09-17 01:14:11Z

2

Unfortunately, the replace value of statement only updates one node at a time. And for a single update the position [0] is wrong, it should be [1]. Check it out a solution below. SQL Server XQuery native out-of-the-box FLWOR expression is a way to do it.

SQL

-- DDL and sample data population, start
DECLARE @xml xml = N'<Container>
  <Collection>
    <foo>One</foo>
    <bar>true</bar>
    <baz>false</baz>
  </Collection>
  <Collection>
    <foo>Two</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
  <Collection>
    <foo>Three</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
</Container>';
-- DDL and sample data population, end

-- before
SELECT @xml;

SET @xml.modify('replace value of ((/Container/Collection)[(foo/text())[1] = "Two"]/bar/text())[1] with "false"')

-- after
SELECT @xml;

DECLARE @bar VARCHAR(10) = 'false';

SET @xml = @xml.query('<Container>
{
    for $y in /Container/Collection
    return <Collection>
    {
        for $z in $y/*
        return 
        if (not(local-name($z) = ("bar"))) then $z
        else 
        (
            element bar {sql:variable("@bar")}
        )
    }
    </Collection>
}
</Container>');

-- after bulk update
SELECT @xml;

edited Sep 17, 2019 at 1:14

answered Sep 17, 2019 at 0:54

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

Again a very good answer, +1 from my side! I think, that the one-liner covers the OP's needs, but the XQuery approach demonstrates, how to push the limits.

pmbAustin Over a year ago

That is such a stupid, typical programmer error on my part... always "off by one". My brain defaults to zero based, and this isn't the first time I've put zero in and just not seen or caught it. So thanks for that! However, the bulk update answer doesn't work for me, because in my REAL LIFE case, there's a ton of other stuff I want to ignore before the series of "Collection" nodes... and that bulk update erases those nodes. Is there a way to JUST update the Collection nodes in bulk, but leave everything else alone?

Yitzhak Khabinsky · Accepted Answer · 2019-09-17 19:16:13Z

1

Here is the answer for the "..REAL LIFE case...". I modified the input XML by adding some additional elements. The XQuery was adjusted accordingly.

SQL

-- DDL and sample data population, start
DECLARE @xml xml = N'<Container>
  <city>Miami</city>
  <state>FL</state>
  <Collection>
    <foo>One</foo>
    <bar>true</bar>
    <baz>false</baz>
  </Collection>
  <Collection>
    <foo>Two</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
  <Collection>
    <foo>Three</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
</Container>';
-- DDL and sample data population, end

-- before
SELECT @xml AS [before];

-- update single element
SET @xml.modify('replace value of (/Container/Collection[upper-case((foo/text())[1]) = "TWO"]/bar/text())[1] with "false"')

-- after
SELECT @xml AS [After];

-- Method #1, via FLWOR expression
-- update all <bar> elements with the false' value
DECLARE @bar VARCHAR(10) = 'false';

SET @xml = @xml.query('<Container>
{
   for $x in /Container/*[not(local-name(.)=("Collection"))]
   return $x
}
{
    for $y in /Container/Collection
    return <Collection>
    {
        for $z in $y/*
        return 
        if (not(local-name($z) = ("bar"))) then $z
        else 
        (
            element bar {sql:variable("@bar")}
        )
    }
    </Collection>
}
</Container>');

edited Sep 17, 2019 at 19:16

answered Sep 17, 2019 at 19:14

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

1 Comment

pmbAustin Over a year ago

Excellent! Thank you!

Ed Bangga · Accepted Answer · 2019-09-17 00:37:08Z

1

to replace all Bar nodes with text() = false, you can try this loop.

declare @ctr int
select @ctr = max(@xml.value('count(//Collection/bar)', 'int'))
while @ctr > 0 
begin   
    set @xml.modify('replace value of ((//Collection/bar)[sql:variable("@ctr")]/text())[1] with ("false")')
    set @ctr = @ctr - 1
end

to replace the first 2 nodes.

set @xml.modify('replace value of ((//Collection/bar)[1]/text())[1] with ("false")')
set @xml.modify('replace value of ((//Collection/bar)[2]/text())[1] with ("false")')

answered Sep 17, 2019 at 0:37

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

2 Comments

Gottfried Lesigang Over a year ago

I'm not sure, but I get the question in a way, that the OP wants to change one element selectively...

pmbAustin Over a year ago

Yeah, I cannot replace by position (the position is unpredictable), I have to replace where a given property in that collection has a specific name (in the one-off update case). But the example of iterating through all the nodes and updating each one is a good one.

Collectives™ on Stack Overflow

How to modify an xml variable with a conditional/where clause

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related