How can I update multiple XML elements within a single document?

For example, if I have the XML below and I want to change any elements with an attribute Store_ID="13" to instead have Store_ID="99".

declare @x xml 
select @x = N'
    <Games>
      <Game>
        <Place City="LAS" State="NV" />
        <Place City="ATL" State="GA" />
        <Store Store_ID="12" Price_ID="162" Description="Doom" />
        <Store Store_ID="12" Price_ID="575" Description="Pac-man" />
        <Store Store_ID="13" Price_ID="167" Description="Demons v3" />
        <Store Store_ID="13" Price_ID="123" Description="Whatever" />
      </Game>
    </Games>
'
select @x 

I can find all the elements with SQL like this:

select t.c.query('.') 
from @x.nodes('.//*[@Store_ID="13"]') as t(c)

To update only the first element I could do an update like this (or change '1' to '2' to update the 2nd element, etc):

SET @x.modify('  
  replace value of (.//*[@Store_ID="13"]/@Store_ID)[1]  
  with "99"  
');  
SELECT @x;

The docs for replace value of say I can only update one node at a time:

It must identify only a single node ... When multiple nodes are selected, an error is raised.

So how do I update multiple elements? I can imagine querying first to find how many elements there are, then looping through and calling @x.modify() once for each element, passing an index parameter... but a) that feels wrong and b) when I try it I get an error

-- Find how many elements there are with the attribute to update
declare @numberOfElements int 
select @numberOfElements = count(*) 
from ( 
    select element = t.c.query('.') 
    from @x.nodes('.//*[@Store_ID="13"]') as t(c)
) x 

declare @i int = 1
declare @query nvarchar(max) 

-- loop through and update each one 
while @i <= @numberOfElements begin 

    SET @x.modify('  
      replace value of (.//*[@Store_ID="13"]/@Store_ID)[sql:variable("@i")]  
      with "99"  
    ');  
    set @i = @i   1 ;  
end 

SELECT @x;

Running the sql above gives me the error:

Msg 2337, Level 16, State 1, Line 31
XQuery [modify()]: The target of 'replace' must be at most one node, found 'attribute(Store_ID,xdt:untypedAtomic) *'

Furthermore, if I'm wanting to run this against many rows of a table with XML data stored in a column, it becomes very procedural.

Otherwise I could cast to nvarchar(max) and do string manipulation on it and then cast back to xml. Again, this feels icky, but also means I don't get the power of xml expressions to find the elements to update.

CodePudding user response：

If the XML structure is defined and well-known ahead of time then you could avoid the XML.modify() limitations by just recreating the XML with the edits applied inline.

Example 1: reconstruct the XML with .nodes(), .values() and FOR XML

update dbo.Example
set x = (
    select
        (
            select Place.value(N'@City', N'nvarchar(max)') as [@City],
                Place.value(N'@State', N'nvarchar(max)') as [@State]
            from Game.nodes('Place') as n(Place)
            for xml path(N'Place'), type
        ),
        (
            select
                case when (StoreID = 13) then 99 else StoreID end as [@Store_ID],
                PriceID as [@Price_ID],
                [Description] as [@Description]
            from Game.nodes('Store') as n(Store)
            cross apply (
                select  Store.value(N'@Store_ID', N'int'),
                    Store.value(N'@Price_ID', N'int'),
                    Store.value(N'@Description', N'nvarchar(max)')
            ) attributes(StoreID, PriceID, [Description])
            for xml path('Store'), type
        )
    from x.nodes(N'/Games/Game') Games(Game)
    for xml path(N'Game'), root(N'Games')
);

Example 2: reconstruct the XML with XQuery

update dbo.Example
set x = (
    select  x.query('
<Games>
    <Game>
        {
            for $place in /Games/Game/Place
                return $place
        }
        {
            for $store in /Games/Game/Store
                let $PriceID := $store/@Price_ID
                let $StoreID_Input := xs:integer($store/@Store_ID) 
                let $StoreID := if ($StoreID_Input != 13) then $StoreID_Input else 99
                let $Description := $store/@Description
                return <Store Store_ID="{$StoreID}" Price_ID="{$PriceID}" Description="{$Description}"/>
        }
    </Game>
</Games>')
);

CodePudding user response：

As per this answer it's not possible to update multiple times in a single statement, so to use xml you need to loop and call modify() repeatedly until all values are updated.

My initial attempt to loop was misfounded: simply do the modify() on the first matching element until there's no more:

while @x.exist('.//*[@Store_ID="13"]')=1
begin 

    SET @x.modify('  
      replace value of (.//*[@Store_ID="13"]/@Store_ID)[1]
      with "99"  
    ');  
end 

In my trivial examples I've used hardcoded values "13" and "99" but for real code you can use sql:variable("@variableName") (ref) and sql:column("tableNameOrAlias.colName") (ref), e.g.

declare @oldId int = 13
    ,   @newId int = 99 

while @x.exist('.//*[@Store_ID=sql:variable("@oldId")]')=1
begin 

    SET @x.modify('  
      replace value of (.//*[@Store_ID=sql:variable("@oldId")]/@Store_ID)[1]
      with sql:variable("@newId")  
    ');  
end