CODEWITHSUNDEEP
sql-serverxmlt-sqlxpath
pmbAustin
pmbAustin

Reputation: 3970

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

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.

Upvotes: 1

Views: 2023

Answers (3)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

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>');

Upvotes: 1

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

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;

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

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")')

Upvotes: 1

Related Questions